Microsoft KB Archive/209250

= ACC2000: Query with Parameters to Evaluate Complex Criteria =

Article ID: 209250

Article Last Modified on 6/30/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209250



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

Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 2002 version of this article, see 290178.



SUMMARY
In Microsoft Access, you can use variable parameters in queries. This article discusses how to construct a query that requires more than one prompt. You can use each parameter both as criteria and as a field to allow complicated evaluation of the value entered in each parameter.



MORE INFORMATION
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.

The following parameter query is based on the Orders table in the sample database Northwind.mdb. It selects orders written between two variable dates provided by the user.

If the user does not enter either the Start Date or the End Date, the query returns all dates greater than or equal to the Start Date, or less than or equal to the End Date. If the user enters neither a Start Date nor an End Date, the query returns all orders.  Open the sample database Northwind.mdb. Create a new query based on the Orders table.  Enter the following query:    Query: FindOrdersByDate -   Type: Select Query

Field: OrderID Show: Yes

Field: OrderDate Sort: Ascending Show: Yes First Criteria Line: Between [Start Date] and [End Date] Second Criteria Line: <=[End Date] Third Criteria Line: >=[Start Date]

Field: [Start Date] Show: No   First Criteria Line: Is Not Null Second Criteria Line: Is Null Third Criteria Line: Is Not Null Fourth Criteria Line: Is Null

Field: [End Date] Show: No   First Criteria Line: Is Not Null Second Criteria Line: Is Not Null Third Criteria Line: Is Null Fourth Criteria Line: Is Null   On the Query menu, click Parameters. In the Query Parameters dialog box, add two entries, one for each parameter in the query, as follows:    Query Parameters ---   Parameter: Start Date Data Type: Date/Time

Parameter: End Date Data Type: Date/Time  To run the query, on the View menu, click Datasheet. Access will prompt you for the value of the parameters and will substitute the proper values in the query.</li></ol>

<div class="references_section">