How to create a parameter query in Access 2002 (304352)


The information in this article applies to:

  • 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.

IN THIS TASK

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

  1. Start Microsoft Access 2002.
  2. On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
  3. On the View menu, click Database Objects, and then click Queries.
  4. In the Database window, click the Invoices query, and then click Design.
  5. 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]

  6. 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.

  7. Close the query without saving it.
back to the top

How to Create a Query with Two or More Parameters

  1. Start Microsoft Access 2002.
  2. On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
  3. On the View menu, click Database Objects, and then click Queries.
  4. In the Database window, click the Invoices query, and then click Design.
  5. Type the following line in the Criteria cell for the OrderDate field.

    Between [Type the beginning date] And [Type the ending date]

  6. 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.

  7. 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 (*).
  1. Start Microsoft Access 2002.
  2. On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
  3. On the View menu, click Database Objects, and then click Queries.
  4. In the Database window, click the Invoices query, and then click Design.
  5. Type the following line in the Criteria cell for the ProductName field:

    LIKE "*" & [Enter products that contain the phrase] & "*"

  6. 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."

  7. Close the query without saving it.
back to the top


REFERENCES

For more information about creating parameter queries, click Microsoft Access Help on the Help menu, type create a parameter query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

This information is also available on the World Wide Web at the following Microsoft Web site:

create a parameter query

For additional information about creating select queries, click the following article number to view the article in the Microsoft Knowledge Base:

304325 How to create a select query in Access 2002

For additional information about creating crosstab queries, click the following article number to view the article in the Microsoft Knowledge Base:

304349 How to create a crosstab query in Access 2002

For additional information about how to convert a select query to an action query, click the following article number to view the article in the Microsoft Knowledge Base:

304354 How to convert a select query to an action query in Access

For additional information about modifying query properties, click the following article number to view the article in the Microsoft Knowledge Base:

304356 How to modify query properties in Microsoft Access

For additional information about using the Like operator in parameter Queries, click the article number below to view the article in the Microsoft Knowledge Base:

294954 ACC2002: How to Use the LIKE Operator in Parameter Queries

For additional information about using forms or custom dialog boxes for parameter queries, click the following article number to view the article in the Microsoft Knowledge Base:

304428 How to use the query by form (QBF) technique

back to the top
Modification Type:MajorLast Reviewed:7/28/2006
Keywords:kbHOWTOmaster kbhowto kbQuery kbdownload KB304352 kbAudITPro
©2004 Microsoft Corporation. All rights reserved.