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 each and 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:
- Create a new query by choosing Create → Other → Query Design.
- From the Show Table dialog box, add the tables you want to use, and then click Close.
This example uses the Customers table. - Choose Query Tools | Design → Show/Hide → Parameters.
The Query Parameters dialog box appears. - Choose a name and data type for your parameter.
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 list. Common choices are Text, Integer, Currency, and Date/Time. - Click OK to close the Query Parameters dialog box.
Now you can use the parameter by name, in the same way that you'd refer to a field in your query. For example, you can add the following filter condition to the State field:
[CustomerState]
Make sure you keep the square brackets so Access knows you're not trying to enter a piece of text.
When you run this query, Access pops open the Enter Parameter Value dialog box, asking for a value. Enter the state you're interested in, and then click OK. Access uses your value for the filter on the State field.
Tip: Even though you can, it's best not to use more than one query parameter in the same query. When you run a query, Access shows a separate Enter Parameter Value dialog box for each value. If you have a handful of parameters, then you need to click your way through an annoying number of windows.
There's no shortage of practical ways to use query parameters. You could adapt a yearly sales query to use whatever year you choose. You could work similar magic to create a single query to show sales from any month.
However, you shouldn't use query parameters to help you out with day-to-day data-entry tasks (like updating a single customer record). Forms, which you'll begin building in Part Four, give you a more powerful way to browse and edit information.