Microsoft KB Archive/109959

= ACC: How to Use Criteria Queries Without Join Lines =

Article ID: 109959

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 Q109959



Moderate: Requires basic macro, coding, and interoperability skills. For a Microsoft Access 2000 version of this article, see 209135.



SUMMARY
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.



MORE INFORMATION
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:

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

     Table: Date Range ---     Field Name: Start Date Data Type: Date/Time Field Name: End Date Data Type: Date/Time   Enter the following data in the Date Range table:

    Start Date: 01-Jul-95 End Date: 01-Aug-95  Create a new query based on the Orders and Date Range tables. Drag all the fields from the Orders table to the query grid.</li>  Enter the following in the Criteria row of the OrderDate column:

<pre class="fixed_text">     >=[Date Range].[Start Date] And <=[Date Range].[End Date] </li> 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).</li></ol>

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.

<div class="references_section">