Parameter Queries

The word parameter is now often used as a cliché in bloated, pompous corporate speech, so you might come to parameter queries with an attitude of skepticism. Be assured that, despite its name, the parameter query is a model of elegant designand extremely useful, to boot.

As you saw in Chapter 8, "Queries," in the usual select query, you enter criteria on the design grid to restrict the records retrieved to those that meet certain conditions. For example, suppose you want to retrieve customer orders from a specific country. You include the ShipCountry (or similarly named) field in your query and enter the countrysay, Canadain the Criteria row of that column.

However, suppose you want to use the same query again and again, but with slightly different criteria. For example, you might have created a query for customer orders that has all the fields, sorts, and so on just the way you want them. But sometimes you want orders from Canada, other times from Mexico, or Guatemala, or Austria. In each case, you would have to open the query in Design view, go to the ShipCountry field, and edit the criteria for the specific country. Do that once a week, and it's no big deal; do it once an hour, and it's a hassle.

The parameter query offers a solution. Instead of editing the criteria each time on the grid, you can have the query prompt you for the country you require. You enter your criteria (that's exactly what it iscriteria) and run your query.

Are Parameter Queries Really Useful?

You might be wondering, "Wouldn't it be better to use a filter than a query for this objective?" I think the advantages of a parameter query over a filter for certain tasks will become apparent as you read through the chapter. Entering criteria in dialog boxes can be easier than creating filters each time. Moreover, remember that a parameter query is a full-fledged query, so you have all the functionality that a query maintains, including greater flexibility for building forms and reports based on the query.

It might still not seem like a parameter query will save you much time over editing the design grid, even if you often need to change the criteria. A simple comparison of a parameter and a nonparameter query should squelch any doubts. You'll first create a "regular" query using criteria, and then you'll create a parameter query. You'll use NiftyLionsChap9.mdb, which you can download to your hard drive.

False Parameter Value Dialogs

During the 1992 presidential campaign, Ross Perot's vice presidential candidate, Adm. James Stockdale, famously said, "Who am I? Why am I here?" If you came here to find out why an Enter Parameter Value dialog box suddenly appeared on your screen from out of nowhere, you're probably asking yourself the same questions right now. None of the discussion thus farand none that followshas any connection to what you were working on when the dialog box appeared.

Several reasons exist for why you might get an Enter Parameter Value dialog box when no parameter query has been created. Possibly the most likely cause is that there's a mistake in a field name somewhere in the query design grid.

For example, let's say you created a calculated field that shows a 12% increase in purchase prices. In the first row of a column on the design grid, you entered this expression:

ExpectedPrice: [Purchase Price]*1.12

That looks good, except that the field name is probably spelled as one wordPurchasePrice, not Purchase Price. The extra space throws Access for a loop and, in a vain cry for help, it offers up the Enter Parameter Value dialog box.

So when this dialog box appears and there's no parameter query in sight, you can usually take that as a sign of trouble. As you check your work, pay special attention to the spelling of field names.

Creating a Nonparameter Query

The Customers table contains contact data for Nifty Lions's customers. Suppose you're the sales manager and you regularly like to review your customer base by individual state. You decide to create a query that includes all the fields from the table, with an additional column to designate state criteria:

1.	Click the Tables button and select the tblCustomers table.
2.	Choose Insert, Query. With Design View selected, click OK.
3.	From the field list, double-click the asterisk (*) to add all the fields to the query.
4.	Choose File, Save and save your query as qryCustByState.
5.	Double-click CustState to add it to the grid. Deselect the check box in the Show row of the design grid. CustState is already included in the records because you added all fields. You'll use this second column only to set criteria, so you deselect Show so its values won't be displayed twice.
6.	Type ca in the Criteria row of the CustState column.
7.	Click the button at the far left end of the toolbar (View). You see only records from California.
8.	Save the query and close it. Suppose that two hours later you want to see all your customers from Colorado.
9.	In the Database window, select qryCustByState and click Design.
10.	Highlight "ca" in the Criteria row of the CustByState field and type co.
11.	Click View or Run to see your records. Your datasheet includes only records from Colorado.
12.	Close the query and save your changes.

Creating a Parameter Query

After continually opening the query in design view and editing criteria, you decide to try a parameter query to accomplish the same task.

1.	In the Database window, click Tables. With tblCustomers selected, choose Insert, Query. With Design View selected, click OK.
2.	From the field list, double-click the asterisk (*) to add all the fields to the query. Double-click CustState to add it to the grid. Deselect the Show check box in the CustState column.
3.	Choose File, Save and save your query as qryCustByStateParameter.
4.	Click in the Criteria row of the State field. Type [Enter state abbreviation:] (see Figure 9.1). Figure 9.1. In the Criteria row, you type the text you want to appear in the Enter Parameter Value dialog box. As you'll soon see, the text between the brackets appears in the Enter Parameter Value dialog box as a prompt for you to enter criteria.
5.	Save your changes and close the query.
6.	In the Database window, click Queries and double-click qryCustByStateParameter.
7.	In the Enter Parameter Value dialog box (see Figure 9.2), type ca and click OK. Figure 9.2. You enter the criteria you want in the Enter Parameter Value dialog box. Customer records from California are retrieved.
8.	Click View to return to Design view. Notice that the design grid is unchangedthe text in the Criteria row of the CustByState field is the same as when you saved it.
9.	Click View to run the query. Type mi in the dialog box. Click OK to see your records. The two customers from Michigan are retrieved. Close the query.

Q&A

Q1:

All right, I understand the convenience of a parameter query. But one thing I don't get. You showed us in Chapter 7, "Find and Filter," how you can see exactly how records have been filtered in a datasheetnamely, by choosing Record, Filter, Advanced Filter/Sort. I tried that when I was viewing the customer records for California, but the design grid was completely blank. Shouldn't there have been criteria indicating that the records were for California?

Q2:

You didn't do any filtering. When you entered ca in the Enter Parameter Value box, it had the same effect as entering ca in the Criteria row for the CustState field. Instead of creating a filter, you ran a query.

Creating a Report Using a Parameter Query

You can quickly create a report based on your parameter query.

1.	With qryCustByStateParameter selected in the Datasheet window, choose Insert, AutoReport.
2.	In the query prompt, type ca. Access creates a report of California customers.
3.	Close the report without saving your changes.

NOTE

You can use a parameter query with a Report Wizard as well. In the New Report dialog box, select Report Wizard and use the parameter query as your record source. Before Access finishes creating the object, it issues the Enter Parameter Value dialog box so you can enter criteria. The same general technique applies to the Form Wizard as well.

Parameter Query with a Wildcard

You can also use a parameter query with a wildcard. For example, let's say you want to see records for customers whose last names begin with a certain letter or letters. Create a query as you would normally do. In the Criteria row of the LastName field, enter Like [Enter the first two letters of last name] & "*". The expression consists of the following elements:

• The Like operator, which tells Access to match the text string

• The prompt text of Enter the first two letters of last name, enclosed in brackets

• The & operator for concatenation (a topic discussed at length in Chapter 10, "Reports")

• A string literal containing the * wildcard, which means any number of characters can follow the first two letters

If you recall studying the Validation Text property for fields, you'll remember that Access doesn't compare the validation text with the actual validation rule. Whatever you enter in Validation Text is what Access displays as an error message when the validation rule is broken.

The prompt text in a parameter query is similar. The contents are valuable and should tell you or the user exactly what to do. But Access doesn't pass judgment on it. You can type [Pink, pink elephants; pink, pink elephants] in the Criteria row of a State field, and Access won't protest.

So if you're using a parameter query with a wildcard, you can make the prompt say whatever you want and thereby suit it to your needs. For example, you can type [Enter the first letter of last name] or [Enter the first few letters of last name]. If you use the first string, you're suggesting to users that they enter b to get records for Barton, Boyton, and Buckner. If you use the latter, you're suggesting they enter bal to get Baldridge, Ballon, and Balderman, or bald to get Baldridge and Balderman.

Parameter Query with Two Values

You can also use parameter queries that require more than one entry. For example, you might have criteria in a single field that requires two entries, such as a Date field that uses the operator Between...And. You could also have a parameter query using criteria in two fields.

Suppose you want to create a parameter query to find the shipping cost for all orders for various time periods. As you recall from Chapter 7, you use the Between...And operator.

1.	In the Database window, click Queries and choose New. With Design View selected, click OK.
2.	Add the tblOrders table to the Design window and close the Show Table dialog box.
3.	Add the OrderID, OrderDate, and ShipCost fields to the grid.
4.	In the Criteria row of the OrderDate field, enter the expression Between [Type the beginning date:] And [Type the ending date:] (see Figure 9.3). Figure 9.3. You can use a parameter query with the Between...And operator.
5.	Choose File, Save. Save the query as qryOrderDatesParameter.
6.	Click View. Because the criteria uses the Between...And operator, you are prompted by two dialog boxes: one for the beginning date and the other for the ending date.
7.	Type 8/20/04 and click OK. Type 8/28/04 and click OK. Your query shows order IDs, order dates, and shipping costs for the period between 8/20/04 and 8/28/04 (there were no orders between 8/20/04 and 8/22/04). Save your changes and close the query.

Now let's try a parameter query using two different fields. We can search for orders on a particular date that had shipping costs between $X and $Y. Here's how to create the query.

1.	In the Database window, click Queries. Double-click Create Query in Design View.
2.	Add tblOrders to the design grid and close the Show Table dialog box.
3.	Add the OrderID, OrderDate, and ShipCost fields to the grid.
4.	In the Criteria row of the OrderDate field, type [Enter the date:].
5.	In the ShipCost field, type the expression Between [Enter the minimum ship cost:] And [Enter the maximum ship cost:] (see Figure 9.4). Figure 9.4. You can use a parameter query with criteria in two fields.
6.	Choose File, Save. Save the query as qryShipCostParameter.
7.	Click Run. In the Enter the Date prompt, type 8/23/04.
8.	In Enter the Minimum Ship Cost, type 4 and click OK. In Enter the Maximum Ship Cost, type 10 and click OK. A reminder: When you enter currency amounts, dollar signs are unnecessary.
9.	The query returns orders on 8/23/04 that have shipping costs of between $4 and $10.
10.	Close the query and save your changes.

Query Parameters Dialog

The Query Parameters dialog box is available by choosing Query, Parameters. In this grid, you can enter the fields that require criteria and the data type of the field.

For creating parameters in fields of most data types, the Query Parameters dialog box is not necessary. But it does offer certain benefits, as you will see presently. It is also required for certain queries, including crosstab queries and in situations when one of the prompts is for a field from a table in an external database.

A Query That Requires the Query Parameters Dialog Box

The Query Parameters dialog box is required in another instance: when you have parameters in two fields and one of them has the Yes/No data type. Try this example:

1.	Create a new query in Design view based on the tblOrders table.
2.	Add the OrderID, ShippedDate, Overnight, and ShipCost fields.
3.	Choose File, Save. Save the query as qryOvernightOrders.
4.	Click View, briefly review the fields and records, and return to Design view.
5.	In the Criteria row of the ShippedDate field, type [Enter the shipped date:].
6.	In the Overnight column, type [Enter Yes or No] in the Criteria row.
7.	Click View. For the date, type 8/24/04 and click OK. For the Overnight parameter, enter yes and click OK. You get an error message that there is a problem with the expressions.
8.	Click OK. Copy [Enter the date:] from the ShippedDate field to the Clipboard.
9.	Choose Query, Parameters. Paste the text in the first row of the first column. The Query Parameters dialog box can also be issued by right-clicking in the top pane and choosing Parameters from the shortcut menu.
10.	Press Tab. Open the drop-down list and select Date/Time. Click OK.
11.	Copy [Enter Yes or No] from the Overnight column.
12.	Choose Query, Parameters. Paste the text in the second row of the first column.
13.	Press Tab. Open the drop-down list and choose Yes/No (see Figure 9.5). Figure 9.5. The Parameters Query dialog box is required when you have parameters in two fields and one has the Yes/No data type.
14.	Click View. Type 8/24/04 in the first prompt and click OK. Type yes in the second prompt and click OK. There are three orders that were shipped overnight.
15.	Close the query and save your changes.

A Query in Which the Query Parameters Dialog Box Is Helpful

Now let's take a look at a typical parameter query that doesn't require the Query Parameters dialog box but one in which it might be helpful.

1.	Create a new query in Design view based on the tblOrders table.
2.	Add the OrderID and OrderDate fields to the design grid.
3.	Choose File, Save and save the query as qryParameterDialog.
4.	Click in the Criteria row of the OrderDate field. Type [Enter date of order:].
5.	Click View. In the Enter Parameter Value dialog box, type 82304. This date is obviously missing slashes, but don't correct it.
6.	Click View. Access returns no records. At this point, you don't know whether there really were no records for 8/23/04 or whether you just made a mistake in entering criteria.
7.	Click View to return to Design view. Copy the contents of the Criteria row in the OrderDate to the Clipboard.
8.	Choose Query, Parameters.
9.	Click in the first column of the first row and paste the contents from the Clipboard.
10.	Press Tab. Open the drop-down list and type da for Date/Time. Click OK.
11.	Click View. In the Enter Parameter Value dialog box, type 82304 and click OK. You see the error message in Figure 9.6. Figure 9.6. The Query Parameter dialog box helps provide an appropriate error message.
12.	Click OK. Edit the criteria to 8/23/04 and click OK to see your records.
13.	Close the query and save your changes.

I don't want to imply that using the Query Parameters dialog box is a cure-all for the sloppy typist. Even if you have made appropriate entries in the dialog box, you might still retrieve no records or the wrong records because you made some mistake in entering criteria. Nevertheless, using the dialog box gives you a head start in resolving criteria errors.