ACC2000: SQL View Does Not Automatically Store 4-Digit Years

ID: Q222459

The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and 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 this to be a problem in the Microsoft products 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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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.


    3. 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:


    6. 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:


    5. 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.

Additional query words: pra 


Keywords          : kbdta 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: May 13, 1999