Parameter Queries in Access

Getting tired of modifying a query every time you want to use a new criterion? A Parameter query is your answer. A Parameter query prompts the user for criteria to use in a query. For example, you could create a Regional Sales query that would ask for the name of the state you want to filter by.

Creating a Parameter query is easy. All you need to do is click the Criteria row of the field that you want to use as a parameter and type a message, enclosed in [brackets], that you want Access to display when you run the query.

1. In Design View, select the fields that you want to see in your query.
   
2. Click the Totals button.
   
3. Click the Total row for the column you want to display the totals for.
   
4. Click the Total row list arrow and select SUM.
   
5. Click the Criteria row for the field you want to use for your parameter criteria and enter the text of the prompt, surrounded by square brackets [ ].

   For example, you could enter Between [Enter start date] and [Enter end date] in the Date field's criteria row.

   If you are using Total rows and are only using a field-such as the Date field—as a criteria field, you'll also want to select the Where option from its Total row so that the field won't appear in the query results.

6. Save and run the query.
7. Enter a criteria value in response to the prompt and click OK.
   

Access displays the results of the query.

Some advanced Access developers use custom-made forms to provide Parameter queries with their information. If developers bind a parameter to the controls on a form (such as [frmCustomers]![Name]), users can fill out one dialog box instead of having to fill out five or six pop-up dialog boxes.