ACC97: Date Criteria in Converted DB Returns Incorrect Results (160503)


The information in this article applies to:

  • Microsoft Access 97
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.

MORE INFORMATION

Steps to Reproduce Behavior


  1. Start Microsoft Access 2.0 or 7.0, and create a new database called Testdate.mdb.
  2. Create the following new table. You do not need to create a primary key. 
            Table: Table1
        -----------------------
        Field Name: TestDate
           Data Type: Date/Time
  3. Open Table1 in Datasheet view, and add the following records: 
            TestDate
        ---------------
        1/1/29
        6/11/09
        5/15/36
  4. Create a new query in Design view based Table1: 
            Query: Query1
        -------------------------
        Type: Select Query
         Field: TestDate
           Table: Table1
           Criteria: >=#01/01/01#
  5. Save the query, and then run it. Note that all three records are returned in the query results.
  6. Close the database, and quit Microsoft Access.
  7. Start Microsoft Access 97, and open the Testdate.mdb database.
  8. In the Convert/Open Database dialog box, click Open Database, and then click OK.
  9. Click OK when you receive the message that says you can't make changes to the database objects.
  10. Run the Query1 query. Note that no records are returned in the query results.
  11. Open Query1 in Design view. Note that the Criteria changed to >=#1/1/2001#.
Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbprb kbusage KB160503
©2003 Microsoft Corporation. All rights reserved.