Microsoft KB Archive/222459

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

Article ID: 222459

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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
 Open the sample database Northwind.mdb. 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.

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

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

</li> Save the query as TestSQLView.</li>  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. </li></ol>

In a Microsoft Access Project
NOTE: Name AutoCorrect does not exist in Access projects. <ol> Open the sample database NorthwindCS.adp.</li> Create a new blank view.</li> On the View menu, point to Show Panes, and then click SQL.</li> In the SQL pane, type the following:

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

</li> Save the view. Note that the date in the SQL statement is not expanded to four digits.</li></ol>

<div class="references_section">