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

• Microsoft Access 2000

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

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

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.

Additional query words: pra

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