ACC2000: Query with Parameters to Evaluate Complex Criteria
ID: Q209250
|
The information in this article applies to:
This article applies only to a Microsoft Access database (.mdb).
Moderate: Requires basic macro, coding, and interoperability skills.
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: Following the steps in this example will modify the sample
database Northwind.mdb. You may want to back up the Northwind.mdb file
and perform 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.
REFERENCES
For more information about Query by Form, click Microsoft Access Help on the
Help menu, type "query by form" in the Office Assistant or the Answer Wizard,
and then click Search to view the topics returned.
For more information about parameter queries, click Microsoft Access Help on the
Help menu, type "parameter queries" in the Office Assistant or the Answer Wizard,
and then click Search to view the topics returned.
Additional query words:
queries ui parameter query
Keywords : kbdta QryParm
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbinfo