You use queries to retrieve specific records from Access databases. On the design grid, designate the fields you want to include in the query; you can then specify criteria to tailor the query for your own information needs.

Like many Access users, however, you may find that you use the same basic query over and over, changing the criteria slightly each time. For example, a sales manager will create a query for new orders, including fields, such as customer, address, product, quantity, and so on. He can then use the same query again and again to show orders for a particular region, customer, time frame, or another variable.

That would mean, however, that each time the sales manager runs a query, he’d have to modify the Criteria section of the design grid. For volume users, such as the sales manager, that’s inefficient and tiresome. The sales manager (and you) has a better option: a parameter query. When you hear the word "parameter” spoken, you may think it’s just a pretentious word pompous people use to mean a number of things. But in Access, it’s really just another word for criteria.

Indeed, a parameter query isn’t much different from any other select query. It only makes it easier to quickly designate criteria, so you don’t have to constantly edit the design grid. Let’s take a look at how to exploit this useful tool.

 Create A Query

Suppose you’re a sales manager at Northwind Trading. You run queries of new orders for various periods on a regular basis. Open the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. Assuming you have made a copy (or have a backup) of the Northwind database, open the file. In the Database window, click the Queries button. Click New. Select Design View and click OK.

In the Show Table box, select Orders and click Add and then select Order Details and click Add. Click Close. Click the Maximize button in the upper-right corner of the window for a better view.

In the Orders field list, double-click OrderID, CustomerID, and RequiredDate. From the Order Details field list, double-click ProductID and Quantity. Click the Sort row of the RequiredDate field and type a for an ascending sort. Click the View button on the Query Design toolbar to see the results. Note that the required date for the first few orders is July 24, 1996. Click the View button again.

 Create A Parameter Query

Ordinarily, you place criteria directly on the design grid. For example, if you wanted to retrieve all orders for July 24, 1996, you would type 7/24/96on the Criteria row of the RequiredDate field.

Here’s how to achieve the same results using a parameter query. On the Criteria row of the RequiredDate field, type [Enter date]. Click View. The Enter Parameter Value dialog box appears. Note that the text is Enter Date, which is the text enclosed in brackets on the Criteria row. Type 7/24/96 and click OK. Access retrieves three records for that date.

Save the query by typing the name Smart Computing Parameter Query and close it. In the Database window, double-click this query to run it. Access opens the same Enter Parameter Value dialog box; you can type the same or another date for this query. Click Cancel.

To review: You created a parameter query by typing text enclosed by brackets on the Criteria row of the RequiredDate field. You could have typed anything in place of the text we typed: What time-span would you like?or Oh, exalted one, choose a date that fills your desires! would have served the same purpose, and neither would have affected the records you retrieved. When you ran the query, Access opened an Enter Parameter Value dialog box with a prompt to enter criteria. You typed a date and clicked OK to run the query.

 Parameter Queries With Two Parameters

Click Design to open the query in Design view. On the design grid, highlight [Enter date] and delete it. Usually, you want to find orders for a period of time, not just one day. To do that, you use the Between . . . And operator. For example, to find orders between July 24, 1996, and November 30, 1996, type the expression Between 7/24/96 And 11/30/96 on the Criteria row of the RequiredDate field. Click View, and you’ll see Access has retrieved orders for this time period. Click View again.

Now let’s create a parameter query that will let you quickly retrieve order details for different time periods. Highlight the criteria on the Criteria row of the RequiredDate column and type Between [Enter the beginning date] And [Enter the ending date]. Click View.

In the first Enter Parameter Value dialog box for the beginning date, type 7/24/96 and click OK. Now, you’ll see the Enter Parameter Value dialog for the ending date. Type 11/30/96 and click OK. Scroll down to see all the orders for the July through November 1996 time-span. Click View.

 Parameter Queries For Other Data Types

Parameter queries are not limited to dates. Highlight the text on the Criteria row of the RequiredDate field and press DELETE. Click in the Criteria row of the Quantity field. Type Between[Enter the minimum quantity] And [Enter the maximum quantity]. Click View. In the Enter Parameter Value dialog box, type 15and click OK. In the second Enter Parameter Value dialog box, type 50 and click OK. Click View.

Select the Quantity field and click the Ascending sort button. Scroll through the records. Only records with quantities between 15 and 50 are included.

Close the query and save your changes. If you want to delete the query from your database, make sure you select it in the Database window, press DELETE, and click Yes.