Back in our August 2002 Quick Study,we introduced you to parameter queries (and if you have little knowledge of the topic, we strongly encourage you to read this article before proceeding). But that article made no mention of the Parameters dialog box because users can successfully run many parameter queries without ever opening it. However, there are some cases in which your query won't work unless you make the appropriate entries. Most notably, you must use the Parameters dialog box for a field that has a Yes/No data type.

Moreover, beyond the specific cases where it's required, the Parameters dialog box can often help ensure that you enter criteria in a parameter query correctly. Let's take a look at two examples: one where the dialog box is required and another where it isn't essential but is nonetheless useful.

 Review The Data

To follow our directions, you'll need to create new objects in the Northwind database, so we suggest that you make a copy of the database file before proceeding. Navigate to the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. Copy Northwind.mdb to the Clipboard and paste it in another folder. Rename the file CopyOfNorthwind.mdb, which you can now open and use without worrying about changing the original file

Next, open the Products table in Design view to review its structure. Note that the table includes the UnitPrice field, which has the Currency data type, as well as the Discontinued field, which has the Yes/No data type. Close the Products table.

 Create A Parameter Query

Let's create a parameter query for the Products table that includes criteria for both the UnitPrice and Discontinued fields. We'll then use it to find available products that cost $18.

In the Database window, with the Products table selected, choose Query from the Insert menu. In the New Query dialog box, make sure Design View is selected and click OK.

Double-click ProductName, SupplierID, CategoryID, UnitPrice, and Discontinued to add the fields to the design grid. In the Criteria row below the UnitPrice heading, type [Enter the unit price]. In the Criteria row below the Discontinued heading, type [Enter yes or no]. Next, click the View button on the far left side of the toolbar above. In the first Enter Parameter Value dialog box that appears, type 18 and click OK (to view products that cost $18). In the second dialog box that appears, type no and click OK (to view available products; in other words, products that haven't been discontinued).

You should then see an error message appear on-screen that indicates Access can't evaluate the query. The parameter query you tried to enter includes a field with a Yes/No data type, and therefore, it requires that you make the appropriate entries in the Parameters dialog box.

 When The Parameters Dialog Box Is Essential

We'll give you an example that demonstrates how to make the required entries in the Parameters dialog box. In the Criteria row below the UnitPrice heading, highlight and copy "[Enter the unit price]" (by using the CTRL-C keyboard command) to the Clipboard. Right-click anywhere in the top pane and choose Parameters from the pop-up menu. (Alternately, you also can choose Parameters from the Query menu.) In the first column of the first row, use the CTRL-V keyboard command to paste "[Enter the unit price]." Press the TAB key. Then, open the drop-down menu, choose Currency, and click OK.

In the Criteria row below the Discontinued heading, copy "[Enter yes or no]" to the Clipboard. Right-click anywhere in the top pane and choose Parameters. In the second row of the first column, paste "[Enter yes or no]." Press the TAB key, open the drop-down menu, choose Yes/No, and click OK (see Figure 1).

Click the View button on the toolbar above. In the Enter Parameter Value dialog box that appears, type 18 and click OK. In the second dialog box that appears, type no and click OK. Access will respond by showing you that there are four products that meet both of the conditions you specified. Save the query (if you wish) and then close it.

 Helpful Uses For The Parameters Dialog Box

Now it's time to follow an example where the Parameters dialog box isn't required, but it is helpful. In the Database window, open the Orders table in Design view and review the structure. Note that the OrderDate field has the Date/Time data type. Close the Orders table for now.

With the Orders table selected in the Database window, choose Query from the Insert menu. In the New Query dialog box, make sure Design View is selected and click OK.

In the Orders field list, double-click the asterisk (*) to add all the fields to the query. Next, double-click OrderDate to add that field to the second column of the design grid. You'll need to use the OrderDate column solely to set criteria, so deselect its Show checkbox so that the field won't display twice.

On the Criteria row of the OrderDate column, type [Enter a date] and click the View button on the toolbar. In the Enter Parameter Value dialog box, type 7/04/96 and click OK. Take note that one order was made on that date.

Click the View button to return to Design view, and then click View again to display the Enter Parameter Value dialog box. Type 704/96 (yes, omitting a forward slash after the 7 means that this criteria is obviously a mistake) in the field and click OK. Of course, Access is unable to retrieve a matching record. However, you'll notice that you didn't receive a message indicating that there was something wrong with the criteria.

Click the View button. Copy "[Enter a date]" from the design grid to the Clipboard, right-click in the top pane, and choose Parameters. In the Query Parameters dialog box that displays, paste "[Enter a date]" in the first column of the first row. Press TAB, open the drop-down menu, choose Date/Time, and click OK.

Click the View button again. Type 704/96 in the dialog box and click OK. This time, you'll receive an error message that tells you the data isn't valid for the field. Click OK. Then, edit the criteria to 7/04/96 so you can retrieve the record. You can now close the query and exit Access.

Now that you know more about the Parameters dialog box, take advantage of its options to improve the way in which you run some of your parameter queries.