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:

Q155669 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#.

Keywords          : kbusage CnvVerDif QryCrit 
Version           : 97
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: April 20, 1999