Microsoft Office Tutorials and References
In Depth Information
Creating Flexible Parameter Queries
from all records in a table (or tables) from all records in your database. You
also like to have queries that show orders from each month.
Rather than create 12 different queries (one for each month), you can create
a parameter query that asks for the month number. Then, as soon as you
enter a month number, the query shows orders for just the month you
specified. In other words, the month number that you’re interested in becomes
a parameter that you define and pass (provide) to the query just before the
To create a parameter query, start by creating a normal select query (as
detailed in Book III, Chapter 1). You can add tables and field names just as
you would in any other query. Then follow these steps to make your query a
1. In the Design View window, click Parameters in the Show/Hide group
of the (Query Tools) Design tab on the Ribbon.
The Query Parameters dialog box appears.
2. Enter a parameter name and its data type in the appropriate columns.
The parameter name can be any name you like, as long as it doesn’t
match the name of a regular or calculated field that’s already included in
the table. The data type matches the type of data that the parameter will
ask for, such as Text for text, Currency for a dollar value, or Date/Time
for a date or time. You can repeat this step to create as many
parameters as you want.
3. Click OK to close the Query Parameters dialog box.
In the QBE grid, you can treat the parameter name the way you do a value
from a field. In fact, you enclose the parameter’s name in square brackets
just as you do a field name.
In Figure 2-14, we created a Month Number parameter that contains an
integer. In the Criteria row for the Order Date field in the QBE grid, we used
the parameter name in the following expression, as shown in Figure 2-14.
The criterion tells the query to show only those records in which the month
of the order date is equal to whatever we type as the Month Number
parameter, as follows:
Month([Order Date]) = [Month Number]
After defining your parameter and using it in the QBE grid, you can save
the query as you do any other query. The parameter doesn’t come into
play until you open the query in Datasheet view. When you do, an Enter
Parameter Value dialog box opens, like the one shown near the top of
Figure 2-15. Type a value for the parameter, and click OK. For the sake of
this example, suppose that you type 9 to view September orders only. Then,