ACC: How to Use Criteria Queries Without Join Lines

Join lines between tables are used to clarify relationships among data in the joined tables and to sort and order the data. However, there are some situations where it is not necessary to have join lines between tables. This article demonstrates a situation where it is not necessary to have a join line between tables.

NOTE: In Microsoft Access version 7.0, change all date references in the example from 1995 to 1993, and in Microsoft Access versions 2.0 and 1.x, change all date references from 1995 to 1991 to get correct results.

The sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0) contains a table called Orders that has a column called Order Date. To see all the orders placed from January 1, 1995 to February 1, 1995, you would have to use a query because there are no join properties for unequal (greater than or less than) comparisons.

One way to see the orders from July 1, 1995 to August 1, 1995, is to create a table that holds the beginning and ending dates of the period you want to see, and then to create a query comparing the fields in that table against the Order Date column in the Orders table. The following example demonstrates how to do this:

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0.)
2. Create the following new table:
   

         Table: Date Range
         -----------------------
         Field Name: Start Date
            Data Type: Date/Time
         Field Name: End Date
            Data Type: Date/Time
   						

3. Enter the following data in the Date Range table:
   

        Start Date: 01-Jul-95
        End Date: 01-Aug-95
   						

4. Create a new query based on the Orders and Date Range tables.
5. Drag all the fields from the Orders table to the query grid.
6. Enter the following in the Criteria row of the OrderDate column:
   

         >=[Date Range].[Start Date] And <=[Date Range].[End Date]
   						

7. Run the query. Note that the query results in a recordset containing 31 records (or 25 in version 7.0, and 34 in versions 2.0 and 1.x).

There are other ways to accomplish this task. One way is to run a parameter query, in which you are prompted for the beginning and ending dates each time you run the query. The drawback to this method though is that the beginning and ending dates are not saved and must be entered each time you run the query. However, you can work around this drawback by using a form to prompt for the beginning and ending dates and then storing the values in the Date Range table. The next time you open the form, the values last used will be displayed and can be changed if necessary.

For more information about passing values from a form to a parameter query, search the Help Index for "Query By Form," or ask the Microsoft Access 97 Office Assistant.

For more information about parameter queries, search the Help Index for "parameter queries."