ACC2: Date Range Parameter Query Returns No Records (131240)


The information in this article applies to:

  • Microsoft Access 2.0
This article was previously published under Q131240
Novice: Requires knowledge of the user interface on single-user computers.

SYMPTOMS

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:

122927 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.
Modification Type:MajorLast Reviewed:6/25/2002
Keywords:kbbug kbusage KB131240
©2002 Microsoft Corporation. All rights reserved.