Microsoft KB Archive/304447

= 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, &quot;a date from&quot; to &quot;a date to.&quot; 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
 Start Microsoft Access, and then open the sample database Northwind.mdb. On the View menu, click Database Objects, and then click Queries. Click the Invoices query, and then click Design. Type the following expression in the Criteria cell for the ShipCountry field. The expression must be enclosed in 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 records for which the ship country is UK. Close the query without saving it.</li></ol>

How to Create a Query with Two or More Parameters
<ol> Start Microsoft Access, and then open the sample database Northwind.mdb.</li> On the View menu, click Database Objects, and then click Queries.</li> Click the Invoices query, and then click Design.</li> Type the following line in the Criteria cell for the OrderDate field:

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

</li> 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.</li> Close the query without saving it.</li></ol>

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 (*). <ol> Start Microsoft Access, and then open the sample database Northwind.mdb.</li> On the View menu, click Database Objects, and then click Queries.</li> Click the Invoices query, and then click Design.</li> Type the following line in the Criteria cell for the ProductName field.

LIKE &quot;*&quot; & [Enter products that contain the phrase] & &quot;*&quot;

</li> 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 &quot;sauce.&quot;</li> Close the query without saving it.</li></ol>

<div class="references_section">