Access 2010: The Missing Manual

Query Parameters

Query parameters are the Access database’s secret weapon. Query parameters let you create supremely flexible queries by intentionally leaving out one (or more) pieces of information. Every time you run the query, Access prompts you to supply the missing values. These missing values are the query parameters.

Usually, you use query parameters in filter conditions. Suppose you want to view the customers who live in a specific state. You could create a whole range of different queries, like NewYorkCustomers, CaliforniaCustomers, OhioCustomers, and so on. If you’re really interested in only a few states, this approach makes sense. But if you want to work with every one, it’s better to create a single query that uses a parameter for the state information. When you run the query, you fill in the state you want to use at that particular moment.

To create a query that uses parameters, follow these steps:

1. Create a new query by choosing Create→Queries→Query Design.

2. From the Show Table dialog box, add the tables you want to use, and then click Close.

   This example uses the Customers table.

3. Choose Query Tools | Design→Show/Hide→Parameters.

   The Query Parameters dialog box appears.

4. Choose a name and data type for your parameter (Figure 7-11).

   You can use any name you want (but don’t choose a name that’s already in use for a field in your query). The data type should match the field on which you’re using the parameter. You set the data type by choosing one of the options in the drop-down ...