ACC2: Date Range Parameter Query Returns No Records

• Microsoft Access version 2.0

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you run a parameter query to find the records in a date range, the resulting recordset is empty.

CAUSE

The recordsset is empty because one of the following conditions is true for the parameter query:

• The parameter query references an indexed Date/Time field in the underlying table.
• The parameter query has a BETWEEN...AND operator in the Criteria cell for the Date/Time field.

RESOLUTION

To work around this problem, use one of the following methods:

• Use an operator other than the BETWEEN...AND operator to specify a date range parameter. For example, use

        >=[start date] and <=[end date]
  

• Define [start date] and [end date] as explicit parameters by adding them to the Query Parameters dialog box and specifying the Date/Time data type.
• Remove the index on the Date/Time field in the query's underlying table.
• Upgrade your installation of Microsoft Access with the Microsoft Access version 2.0 Service Pack.

STATUS

This problem no longer occurs with the Microsoft Jet database engine version 2.5, which is available with the Microsoft Access version 2.0 Service Pack. For information about how to obtain the Service Pack, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q122927
   TITLE     : WX1124: Microsoft Access Version 2.0 Service Pack

MORE INFORMATION

Steps to Reproduce Problem

1. Create a table with the following structure:

         Table: TestDates
         -------------------------------
         Field Name: ID
            Data Type : Counter
            Indexed: No
         Field Name: Date
            Data Type : Date/Time
            Indexed: Yes (Duplicates OK)
   
   

2. Save the table as TestDates. When Microsoft Access prompts you whether to create a primary key, choose the No button.

3. View the TestDates table in Datasheet view and type the following six records in the Date field:

         12/23/94
         12/25/94
         12/30/94
         1/1/95
         1/1/92
         12/31/93
   

4. Create the following query based on the TestDates table:

         Query: Test1
         -----------------------------------------------------------------
         Field: ID
             Show: Yes
         Field: Date
             Show: Yes
             Criteria: Between [Enter a start date] And [Enter an end date]
   
   

5. Run the Test1 query and, when prompted, type the following dates:

         [Enter a start date]: 12/23/94
         [Enter an end date]: 1/1/95
   

   Note that you receive zero records rather than the four records you expect.

REFERENCES

For more information on parameter queries, search for "parameter query," and then "Creating a Parameter Query" using the Microsoft Access Help menu.