As explained in "Writing International Code in Visual Basic," different countries have different date formats. If your application performs a comparison between two dates, date literals must be stored in a unique format to ensure a reliable comparison, regardless of a user's locale. In Visual Basic, the database engine stores a date/time value as a DateSerial value, which is represented by an 8-byte floating-point number, with the date as the integral portion and the time as the fractional portion. This approach is completely locale-independent and will let you perform date/time comparisons using the international date/time formats.
Structured Query Language (SQL) is an ANSI standard with which Visual Basic complies. Dates are saved in tables and databases using the English/U.S. format (month/day/year). This format was also adopted for the Microsoft Jet database engine. Queries that use these fields may return the wrong records or no records at all if a non-U.S. date format is used.
This constraint also applies to the Filter property, to the FindFirst, FindNext, FindPrevious, and FindLast methods of the Recordset object, and to the WHERE clause of an SQL statement.
There are two functions you can use to handle the limitations of the SQL standard. Avoid using date/time literals in your code. Instead, consider using the DateValue or the DateSerial functions to generate the date you want. The DateValue function uses the system's Short Date setting to interpret the string you supply; the DateSerial function uses a set of arguments that will run in any locale. If you are using date/time literals in your SQL query or with the Filter property, you have no choice but to use the English/U.S. format for date and time.
The following examples illustrate how to perform a query based on a date. In the first example, a non-U.S. date format is used. The Recordset returned is empty because there is a syntax error in the date expression:
Dim mydb As Database
Dim myds As Recordset
Set mydb = OpenDatabase("MyDatabase.mdb")
' Table that contains the date/time field.
Set myds = mydb.OpenRecordset("MyTable,dbopenDynaset")
' The date format is dd/mm/yy.
myds.FindFirst "DateFiled > #30/03/97#"
' A data control is connected to mydb.
Data1.Recordset.Filter = "DateFiled = #30/03/97#"
mydb.Close
myds.Close
The following example, however, will work adequately in any locale because the date is in the appropriate format:
Dim mydb As Database
Dim myds As Recordset
Set mydb = OpenDatabase("MyDatabase.mdb")
' Table that contains the date/time field.
Set myds = mydb.OpenRecordset("MyTable, dbopenDynaset")
myds.FindFirst "DateFiled > #03/30/97#" ' Date format
' is mm/dd/yy.
' A data control is connected to mydb.
Data1.Recordset.Filter = "DateFiled = _
DateValue(""" & DateString & """)"
mydb.Close
myds.Close