Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/103252

From BetaArchive Wiki
Knowledge Base


ACC: Query with Parameters to Evaluate Complex Criteria

Article ID: 103252

Article Last Modified on 1/18/2007



APPLIES TO

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q103252

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. Each parameter can be used both as criteria and as a field to allow complicated evaluation of the value entered in each parameter.

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.


REFERENCES

For more information about these kinds of queries, search the Help Index for "Query by Form," or "Parameter Queries," or ask the Microsoft Access 97 Office Assistant.


Additional query words: queries ui parameter query

Keywords: kbinfo KB103252