Wildcard characters in a query serve as a placeholder for one or more unknown characters in your criteria. When you are unsure of the particular character or set of characters to include in your criteria, you can use wildcard characters in place of the characters in the criteria.
Activity 2.19. Using a Wildcard in a Query
Use the asterisk (*) to represent any group of characters. For example, if you use the * wildcard in the criteria Fo*, the results would return Foster, Forrester, Forrest, Fossil, and so forth. In this activity, you will use the asterisk * wildcard and specify the criteria in the query so that only pledges due from donors in the 312 area code display.
1.
With the 2A_Club Fundraiser database open, and Queries selected on the Objects bar, open your 2A Pledges Due query in Datasheet view.
Notice that the donors who have not paid their pledges have phone numbers in several area codes773, 708, and 312.
[Page 1010]
2.
Click the View button . On the Criteria row, under Donor Phone, type "(312)*" and then press . Compare your screen with Figure 2.40.
Figure 2.40.
The wildcard character * is used as a placeholder to match any number of characters. When you press , Like is added by Access at the beginning of the criteria. This is used to compare a sequence of characters and test whether or not the text matches a pattern.
Access will automatically insert expressions similar to this when creating queries. The quote marks are used because of the parentheses in the criteria. Recall that Access adds quote marks to text criteria to identify them as a string of characters. Because parentheses are also used in Access as programming characters, in this example you must type the quote marks to indicate to Access that the parentheses should be included as characters in the criteria.
NoteStructured Query Language
SQL (Structured Query Language) is a language used in querying, updating, and managing relational databases. The term Like is used in SQL to compare string expressions. In Access, the term expression is the same thing as a formula. A string expression looks at a sequence of characters and compares them to the criteria in a query. You will learn more about SQL as you progress in your study of Access.
3.
Run the query.
The query results display three records. Three donors in the (312) area code must be contacted regarding their pledge.
[Page 1011]
4.
Return to the Design view . Click in the Table row under Email Address, click the Table arrow, and then click to select Pledges. On the Field row, in the same column, click to display the Field arrow, and then click Club Affiliation.
In this manner, you can select a new field from a different field list to display in this position in the query.
Another Way: To Change Fields in a Query
Select and Delete Fields
Another way to change fields in a query is to first select the field by clicking the selection bar above the field name, and then pressing . You can add fields as you have practiced by double-clicking the field name, which adds the field in the next available field position, or by dragging the field to the desired spot in the field row.
5.
In the Criteria row, under Donor Phone, select and then delete the existing text Like "(312)*".
6.
In the Criteria row, under Club Affiliation, type *Club and then press . Compare your screen with Figure 2.41.
Figure 2.41.
The * can be used at the beginning or end of the criteria. The position of the wildcard determines the location of the unknown characters. Here you will search for records of organizations with Club as the last word in the name. This time Access added Like and the quote marks around the criteria.
7.
Run the query.
Five of the pledges due are for clubs. Notice that Club is the last word in the organization name in the Club Affiliation column. In this manner you can use the asterisk to look for a value that may be in the beginning (Club*), end (*Club) or anywhere in the data (*Club*) in a record.
[Page 1012]
8.
From the File menu, display the Save As dialog box, type 2A *Clubs Firstname Lastname and then click OK to save the query with a new name.
Recall that when you start with an existing query, you must choose Save As from the File menu to preserve the original query and save the new one with a new name.
9.
From the File menu, display the Page Setup dialog box, click the Page tab, and then under Orientation, click Landscape. Click OK.
Although the original query was set to print in landscape, you must reset the page orientation each time you print; page orientation is not stored with the query.
10.
If you have been instructed to submit your files electronically, close the query and skip this step. Otherwise, Print the results, and then Close the Query window.
More Knowledge: Search for a Single Unknown Character
Using the? Wildcard in a Query
The question mark (?) is another wildcard that is used to search for unknown single characters. For each question mark included in a criteria, any character can be inserted. For example, if you used b?d as a criteria, the query could locate bid, bud, bed. If b??d is entered as the criteria, the results could include bind, bend, or bard.