How to create a parameter query in Access 2000 (304353)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q304353
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 2002 version of this article, see 304352.

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 2000, and then open the sample database Northwind.mdb.
  2. On the View menu, click Database Objects, and then click Queries.
  3. In the Database window, click the Invoices query, and then click Design.
  4. 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]

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

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

How to Create a Query with Two or More Parameters

  1. Start Microsoft Access 2000, and then open the sample database Northwind.mdb.
  2. On the View menu, click Database Objects, and then click Queries.
  3. In the Database window, click the Invoices query, and then click Design.
  4. Type the following line in the Criteria cell for the OrderDate field.

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

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

  6. 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 2000, and then open the sample database Northwind.mdb.
  2. On the View menu, click Database Objects, and then click Queries.
  3. In the Database window, click the Invoices query, and then click Design.
  4. Type the following line in the Criteria cell for the ProductName field:

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

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

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


REFERENCES

For more information about how to create queries in Microsoft Access, click the "Working with Queries" topic on the Microsoft Access 2000 Help page at the following Microsoft Web site:

Microsoft Access 2000 Help

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

209645 ACC2000: How to Use the Query by Form (QBF) Technique

For additional information about creating crosstab queries with multiple value fields, click the article number below to view the article in the Microsoft Knowledge Base:

209143 ACC2000: Creating a Crosstab Query with Multiple Value Fields

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

304361 ACC2000: How to Create a Select Query in Microsoft Access

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

304352 HOW TO: Create a Parameter Query in Microsoft Access 2002

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

304355 ACC2000: How to Convert a Select Query to an Action Query

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

304357 ACC2000: How to Modify Query Properties in Microsoft Access

To download a sample Microsoft Access database that contains over 20 query examples, including the techniques described in these articles, see the following article in the Microsoft Knowledge Base:

207626 ACC2000: Access 2000 Sample Queries Available in Download Center



back to the top
Modification Type:MinorLast Reviewed:8/9/2004
Keywords:kbwizard kbQuery kbdownload kbHOWTOmaster KB304353 kbAudITPro
©2004 Microsoft Corporation. All rights reserved.