Microsoft KB Archive/304447

Knowledge Base

ACC97: How to Create a Parameter Query in Microsoft Access

Article ID: 304447

Article Last Modified on 1/31/2007

---

APPLIES TO

• Microsoft Access 97 Standard Edition

---

This article was previously published under Q304447

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

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

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.

MORE INFORMATION

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 and reports. 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 database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

How to Create a Query with One Parameter

1. Start Microsoft Access, and then open the sample database Northwind.mdb.
2. On the View menu, click Database Objects, and then click Queries.
3. Click the Invoices query, and then click Design.
4. Type the following expression in the Criteria cell for the ShipCountry field. The expression must be enclosed in 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 records for which the ship country is UK.
6. Close the query without saving it.

How to Create a Query with Two or More Parameters

1. Start Microsoft Access, and then open the sample database Northwind.mdb.
2. On the View menu, click Database Objects, and then click Queries.
3. 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/1995, and then click OK. When you are prompted for the ending date, type 1/31/1995, and then click OK to view the results of the query.
   
   Note that the query returns records whose order date is in January 1995.
6. Close the query without saving it.

How to Create Parameters That Use Wildcards

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

1. Start Microsoft Access, and then open the sample database Northwind.mdb.
2. On the View menu, click Database Objects, and then click Queries.
3. 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 records whose the product name contains the word "sauce."
6. Close the query without saving it.

REFERENCES

For more information about creating queries, click Microsoft Access Help on the Help menu, type create a query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
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:

95931 ACC: How to Use the Query-by-Form (QBF) Technique

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

304444 ACC97: How to Create a Select Query in Microsoft Access

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

304446 ACC97: How to Create a Crosstab Query in Microsoft Access

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:

304448 ACC97: 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:

304451 ACC97: How to Modify Query Properties in Microsoft Access

You can also view queries for most of the techniques described in these articles in the sample file QrySmp97.exe from the following article:

182568 ACC97: Microsoft Access 97 Sample Queries Available in Download Center

Additional query words: inf

Keywords: kbhowto KB304447

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.