HOW TO: Create a Parameter Query in Microsoft Access 2002

This article was previously published under Q304352

Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2000 version of this article, see 304353.

SUMMARY

You can use criteria in a query in Microsoft Access to restrict the set of records that the query returns. To query for different records each time that you run a particular query, you may want the query to prompt you for criteria to enter, for example, "a date from" to "a date to." A query that prompts you for criteria is called a parameter query. This article shows you how to create parameter queries in Microsoft Access.

A parameter query displays its own dialog box that prompts you for information. You can design a query to prompt you for one piece of information, for example, a part number, or for more than one piece of information, for example, two dates. Microsoft Access will then retrieve all the records that contain that part number or all the records that fall between those two dates.

You can also use parameter queries as the basis for forms, reports, and data access pages. For example, you can create a monthly earnings report based on a parameter query. When you print the report, Access displays a dialog box asking for the month that you want the report to cover. You enter a month, and then Access prints the appropriate report.

You can also do the following with parameter queries, forms, and reports:

Create a custom form or dialog box that prompts for a query's parameters (rather than using the parameter query's dialog box), and then display the results in a datasheet. This is known as Query by Form.
Print the criteria that is entered in a parameter query for a report in the report header, so that you can determine from the report which values were used to create it.

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

back to the top

How to Create a Query with One Parameter

Start Microsoft Access 2002.
On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
On the View menu, click Database Objects, and then click Queries.
In the Database window, click the Invoices query, and then click Design.
Type the following line in the Criteria cell for the ShipCountry field. Note that the expression that you enter must be enclosed in square brackets.
[View invoices for country]
On the Query menu, click Run. When you are prompted, type UK, and then click OK to view the results of the query. Note that the query returns only those records whose ship country is UK.
Close the query without saving it.

back to the top

How to Create a Query with Two or More Parameters

Start Microsoft Access 2002.
On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
On the View menu, click Database Objects, and then click Queries.
In the Database window, click the Invoices query, and then click Design.
Type the following line in the Criteria cell for the OrderDate field.
Between [Type the beginning date] And [Type the ending date]
On the Query menu, click Run. When you are prompted for the beginning date, type 1/1/1997, and then click OK. When you are prompted for the ending date, type 1/31/1997, and then click OK to view the results of the query. Note that the query returns only records whose order date is in January 1997.
Close the query without saving it.

back to the top

How to Create Parameters That Use Wildcards

The following example shows you how to create parameters that use the LIKE operator and the wildcard symbol (*).

Start Microsoft Access 2002.
On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
On the View menu, click Database Objects, and then click Queries.
In the Database window, click the Invoices query, and then click Design.
Type the following line in the Criteria cell for the ProductName field:
LIKE "*" & [Enter products that contain the phrase] & "*"
On the Query menu, click Run. When you are prompted, type sauce, and then click OK to view the results of the query. Note that the query returns only those records whose product name contains the word "sauce."
Close the query without saving it.

back to the top