Creating and Running Parameter Queries

You might not always know the parameters for the query output when you're designing a query—and your application's users also might not know the parameters. Parameter queries let you specify specific criteria at runtime so that you don't have to modify the query each time you want to change the criteria.

For example, imagine you have a query, like the one shown in Figure 15.7, for which you want users to specify the date range they want to view each time they run the query. You have entered the following clause as the criterion for the OrderDate field:

Between [Enter Starting Date] And [Enter Ending Date]

This criterion causes two dialog boxes to appear when the user runs the query. The first one, shown in Figure 15.8, prompts the user with the text in the first set of brackets. Access substitutes the text the user types for the bracketed text. A second dialog box appears, prompting the user for whatever is in the second set of brackets. Access uses the user's responses as criteria for the query.

Task: Building a Parameter Query

Let's take a look at the process of building a parameter query. In this task, you're going to add a parameter to the query qryCustomerOrderSummary so that you can view only TotalPrice summaries within a specific range.

To make sure Access understands what type of data the user should place in these parameters, you must define the parameters. You do this by selecting Query | Parameters to open the Query Parameters dialog box. Another way to display the Query Parameters dialog box is to right-click a gray area in the top half of the query grid and then select Parameters from the context menu.

You must enter the text that appears within the brackets for each parameter in the Parameter field of the Query Parameters dialog box. You must define the type of data in the brackets in the Data Type column. Figure 15.9 shows an example of a completed Query Parameters dialog box.

You can easily create parameters for as many fields as you want. You add additional parameters just as you would add more criteria. For example, the query shown in Figure 15.10 contains parameters for the Title, HireDate, and City fields in the Employees table from the Northwind database. Notice that all the criteria are on one line of the query grid, which means that all the parameters entered must be satisfied in order for the records to appear in the output. The criterion for the title is [Please Enter a Title]. This means that the records in the result must match the title the user enters when he or she runs the query. The criterion for the HireDate field is >=[Please Enter a Hire Date]. Only records with a hire date on or after the hire date the user enters when he or she runs the query appear in the output. Finally, the criterion for the City field is [Please Enter a City]. This means that only records with City containing the value the user enters when he or she runs the query will appear in the output.

The criteria for a query can also be the result of a function.

Task: Building a Parameter Query That Groups by Client

In this task, you build a query based on the tblTimeCardHours table, which is available in the Chap15.mdb sample database. This query gives you the total billing amount, by project, for a specific date range. Figure 15.11 shows the query's design. Notice that this query is a Totals query (also called an aggregate function) that groups results by project and totals the billable hours multiplied by the billing rate. Here's how it works:

The second query is based on tblClients, tblProjects, and tblTimeCardHours. This query gives you the total billing amount, by client, for a specific date range. The query's design is shown in Figure 15.13. This query is an aggregate function that groups results by the company name from the tblClients table and totals by using the following expression:

BillAmount: Sum([BillableHours]*[BillingRate])

As with the first query, qryBillAmountByProject, this query uses the DateWorked field as the Where clause for the query, and it defines the parameters in the Query Parameters dialog box. Save this query as qryBillAmountByClient.