ACC97: Date Criteria in Converted DB Returns Incorrect Results

• Microsoft Access 97

SYMPTOMS

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

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:

   ARTICLE-ID: Q155669
   TITLE     : 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#.