ACC: Query with Parameters to Evaluate Complex Criteria

MORE INFORMATION

The following parameter query is based on the Orders table in the sample database Northwind.mdb (or NWIND.MDB in earlier versions). It selects orders written between two variable dates provided by the user.

If either the Start Date or the End Date is not entered, the query returns all dates greater than or equal to the Start Date, or less than or equal to the End Date. If neither a Start Date nor an End Date is entered, the query returns all orders.

1. Open the sample database Northwind.mdb (or NWIND.MDB in earlier versions).
2. Create a new query based on the Orders table.
3. Enter the following query:

         Query: FindOrdersByDate
         -----------------------------------------------------------
         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
   					

4. On the Query menu, click Parameters. In the Query Parameters 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
   					

5. To run the query, on the View menu, click Datasheet. Microsoft Access will prompt you for the value of the parameters and will substitute the proper values in the query.