Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: Query with Parameters to Evaluate Complex Criteria

ACC2000: Query with Parameters to Evaluate Complex Criteria

View products that this article applies to.

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.

↑ Back to the top

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.
  1. Open the sample database Northwind.mdb.
  2. Create a new query based on the Orders table.
  3. 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
  4. 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
  5. 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.

↑ Back to the top

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.

↑ Back to the top

Article Info
Article ID : 209250
Revision : 3
Created on : 1/1/0001
Published on : 1/1/0001
Exists online : False
Views : 273