ACC2000: SQL View Does Not Automatically Store Four-Digit Years (222459)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q222459
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

When you create a query by using the CreateQueryDef method in Visual Basic for Applications or by typing a SQL statement directly into the SQL View when Track name AutoCorrect info is turned off, date criteria entered as two-digit dates may not be resolved into four-digit dates when you save the query. This may result in the query returning different data sets on different computers, depending on the systems settings of the computer running the query.

CAUSE

Queries entered using the CreateQueryDef method or in the SQL View with Track name AutoCorrect info turned off are saved directly as text, and then evaluated at run-time. Two-digit dates entered as criteria under these conditions will be evaluated to four-digit dates based on the system settings of the computer running the query.

RESOLUTION

Enter your dates with a four-digit year, such as #4/19/1999# in a database or '4/19/1999' in a Microsoft Access project.

-or-

Create your queries using the Access Query By Example (QBE) grid in an Access database or the View Designer in an Access project.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

When you have Track name AutoCorrect info turned on in an Access database, two-digit dates that you type directly into the SQL View will resolve to four-digit dates when you save and close. Track name AutoCorrect info does not affect Access projects or the use of the CreateQueryDef method.

Steps to Reproduce Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

In a Microsoft Access Database

  1. Open the sample database Northwind.mdb.
  2. Make sure that Track name AutoCorrect info is turned off.NOTE: The Track name AutoCorrect info feature is located on the General tab of the Options dialog box.

  3. Create a new blank query.
  4. On the View menu, click SQL View.
  5. In the SQL view, type the following:

    SELECT OrderDate FROM Orders WHERE OrderDate >= #1/1/01#

  6. Save the query as TestSQLView.
  7. In the Immediate window, type the following line and press ENTER: 
    ?CurrentDB.QueryDefs("TestSQLView").SQL
    Note that the date in the SQL statement is not expanded to four digits.

In a Microsoft Access Project

NOTE: Name AutoCorrect does not exist in Access projects.
  1. Open the sample database NorthwindCS.adp.
  2. Create a new blank view.
  3. On the View menu, point to Show Panes, and then click SQL.
  4. In the SQL pane, type the following:

    SELECT OrderDate FROM Orders WHERE OrderDate >= '1/1/01'

  5. Save the view. Note that the date in the SQL statement is not expanded to four digits.

REFERENCES

For more information about Name AutoCorrect, click Microsoft Access Help on the Help menu, type Name AutoCorrect in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kbbug kbpending KB222459
©2004 Microsoft Corporation. All rights reserved.