Microsoft KB Archive/160503

= ACC97: Date Criteria in Converted DB Returns Incorrect Results =

Article ID: 160503

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q160503



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



SYMPTOMS
You have a Microsoft Access 2.0 or 7.0 database that uses a literal date between 1900 and 1929 as criteria in a query. After you convert or enable that database in Microsoft Access 97, the query returns the wrong records or no records at all.

For information about the same symptoms occurring in Microsoft Access 7.0, please see the following article in the Microsoft Knowledge Base:

155669 ACC: Years 01-29 Default to Year 2000 When Typed As M/D/YY



CAUSE
You have a newer version of Oleaut32.dll, which may have been installed by Microsoft Internet Explorer version 3.0 or Microsoft Windows NT version 4.0.



RESOLUTION
You can work around this behavior in the following ways:


 * Change the query criteria to specify the century. For example, change #01/01/00# to #01/01/1900#.
 * Use the type conversion function, CDate, to convert a string to a date in your criteria. For example, CDate("01/01/1901").



STATUS
This behavior is by design.



Steps to Reproduce Behavior
 Start Microsoft Access 2.0 or 7.0, and create a new database called Testdate.mdb.  Create the following new table. You do not need to create a primary key.

       Table: Table1 ---       Field Name: TestDate Data Type: Date/Time   Open Table1 in Datasheet view, and add the following records:

       TestDate ---       1/1/29        6/11/09        5/15/36                          Create a new query in Design view based Table1:

<pre class="fixed_text">       Query: Query1 -       Type: Select Query Field: TestDate Table: Table1 Criteria: >=#01/01/01# </li> Save the query, and then run it. Note that all three records are returned in the query results.</li> Close the database, and quit Microsoft Access.</li> Start Microsoft Access 97, and open the Testdate.mdb database.</li> In the Convert/Open Database dialog box, click Open Database, and then click OK.</li> Click OK when you receive the message that says you can't make changes to the database objects.</li> Run the Query1 query. Note that no records are returned in the query results.</li> Open Query1 in Design view. Note that the Criteria changed to >=#1/1/2001#.</li></ol>

Keywords: kbprb kbusage KB160503

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.