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.
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.
Go to the criteria for TotalPrice and type Between [Please Enter Starting Value] and [Please Enter Ending Value]. This allows you to view all the records for which the total price is within a specific range. Access replaces the bracketed text with actual values when you run the query.
Click OK and run the query. Access prompts you to enter both a starting and an ending value.
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.
Parameter queries offer significant flexibility because they allow the user to enter specific criteria at runtime. What you type in the Query Parameters dialog box must exactly match what you type within the brackets; otherwise, Access prompts the user with additional dialog boxes.
You can add as many parameters as you like to a query, but the user might become bothered if Access presents too many dialog boxes. Instead, you should build a custom form that feeds the Parameter query.
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:
Enter the following expression:
Add the Where clause, using the DateWorked field as the Where clause for the query; here's the criterion for the Where clause:
Between [Enter Start Date] And [Enter End Date]
The example uses the Query Parameters dialog box to declare the two parameters of the criteria (see Figure 15.12).
Save this query as qryBillAmountByProject.
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:
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.