Microsoft Office Tutorials and References
In Depth Information
| Creating a More Flexible Parameter Query
Most users want parameter queries to display the records that match their entered param-
eter value or to display all records when they don’t enter a parameter value. To provide this
functionality, you can change the Criteria text box in the design grid for the specified
column. For example, you could change an entry for a City field from [Enter the city:] to
Like [Enter the city:] & “*” . That is, you can prefix the Like operator to the original crite-
rion and concatenate the criterion to a wildcard character. When you run the parameter
query with this new entry, Access will display one of the following recordsets:
• If you enter a specific City field value in the dialog box, such as Saugatuck, the entry is
the same as Like “Saugatuck” & “*” , which becomes Like “Saugatuck*” after the con-
catenation operation. That is, Access selects all records whose City field values have Saug-
atuck in the first nine positions and any characters in the remaining positions. If the table
on which the query is based contains records with City field values of Saugatuck, Access
displays only those records. However, if the table on which the query is based also con-
tains records with City field values of Saugatuck City, then Access would display both the
Saugatuck and the Saugatuck City records.
• If you enter a letter in the dialog box, such as S, the entry is the same as Like “S*” , and
the recordset displays all records with City field values that begin with the letter S.
• If you enter no value in the dialog box, the entry is the same as Like Null & “*” , which
becomes Like “*” after the concatenation operation, and the recordset displays all records.
Now you’ll modify the parameter query to satisfy Sarah’s request and test the new ver-
sion of the query.
To modify and test the parameter query:
1. Switch to Design view.
2. Click the City Criteria text box, and then open the Zoom dialog box.
You’ll use the Zoom dialog box to modify the value in the City Criteria text box.
3. Click to the left of the expression in the Zoom dialog box, type Like , press the
spacebar , press the End key, press the spacebar , and then type & "*" .See
Modified City Criteria value in the Zoom dialog box
Now you can test the modified parameter query.
4. Click the OK button to close the Zoom dialog box, save your query design changes,
and then run the query.
First, you’ll test the query to display customers in Saugatuck.
5. Type Saugatuck , and then press the Enter key. The recordset displays the data for
the three customers in Saugatuck.
Now you’ll test the query without entering a value when prompted.
6. Switch to Design view, run the query, and then click the OK button. The recordset
displays all 42 original records from the tblCustomer table.