Microsoft KB Archive/131240

= ACC2: Date Range Parameter Query Returns No Records =

Article ID: 131240

Article Last Modified on 6/25/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



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.

<div class="status_section">

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

<div class="moreinformation_section">

Steps to Reproduce Problem
<ol>  Create a table with the following structure:

<pre class="fixed_text">     Table: TestDates ---     Field Name: ID         Data Type : Counter Indexed: No     Field Name: Date Data Type : Date/Time Indexed: Yes (Duplicates OK) </li> Save the table as TestDates. When Microsoft Access prompts you whether to create a primary key, choose the No button.</li>  View the TestDates table in Datasheet view and type the following six records in the Date field:

<pre class="fixed_text">     12/23/94 12/25/94     12/30/94      1/1/95      1/1/92      12/31/93                        </li>  Create the following query based on the TestDates table:

<pre class="fixed_text">     Query: Test1 -     Field: ID          Show: Yes Field: Date Show: Yes Criteria: Between [Enter a start date] And [Enter an end date] </li>  Run the Test1 query and, when prompted, type the following dates:

<pre class="fixed_text">     [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. </li></ol>

<div class="references_section">