Dates entered as variables n MS Access give unpredictable results in SQL queries

[Bruce Callander]

This article results from a problem I encountered in an Access database.

SQL assumes that dates are in US format: MM/DD/YYYY. Suppose that a date is entered into a MS Access form and then used in an underlying SQL query. If the date is in UK format DD/MM/YYYY then unpredictable results can occur because Access attempts to interpret the date as if it were in US format. If a valid date results then the procedure proceeds with an altered date. If the original date cannot be interpreted as a valid date in US format then the original date is retained.

An example will illustrate.

A date is entered on an Access form in a control named test_date. The entered date is 06 March 2010, corresponding to 06/03/2010 in UK format.

Suppose the date is included in a SQL statement within some underlying VBA, in a statement such as

"SELECT [field_1] FROM tbl_Events WHERE [date_Event]<#" & test_date & "#;"
The SQL statement that will actually be executed is

"SELECT [field_1] FROM tbl_Events WHERE [date_Event]<#03/06/2010#;"
which is not the required result.

On the other hand if the date had been 31 Mar 2010 the SELECT statement would correctly be

"SELECT [field_1] FROM tbl_Events WHERE [date_Event]<#31/03/2010#;"
because interpreting 31/03/2010 into US format does not result in a valid date - so the date is left unchanged.

The following subroutine ensures that dates are always presented to a SQL query in the expected US format.

    Public Function DateforSQL(thisDate As Date) As String
'
    'This function returns a string representing the date thisDate (in UK dd/mm/yyyy format)
    ' converted to US format - mm/dd/yyyy. It is necessary because MS Access (or its underlying
    ' JET engine?), assumes that dates are in US format
 
    Dim dayStr As Integer, monthStr As Integer, yearStr As Integer
 
    dayStr = Day(thisDate)
    ' This function returns the day of the month as an integer
 
    monthStr = Month(thisDate)
    ' This function returns the month, as an integer
 
    yearStr = Year(thisDate)
    ' This function returns the year, as an integer
 
    DateforSQL = monthStr & "/" & dayStr & "/" & yearStr
 
    Exit Function
    End Function
 

DateforSQL is a String variable and so is used in a VBA SQL statement like this:

"SELECT [field_1] FROM tbl_Events WHERE [date_Event]<#'" & DateforSQL(test_date) & "'#;"

[Killer42]

I realise this insight has been here for quite a while. But I'd just like to add that you can prevent a lot of these sort of date-related issues by using a less ambiguous date format. For example using a non-ambiguous date format can prevent a lot of problems.

For example, #06/07/08# could be all sorts of perfectly valid dates, depending on where you come from. I recommend something like #06-Jul-2008#. The month name and the four-digit year remove any confusion (at least, assuming an English-speaking country).

[MikeTheBike]

Being in the UK this is a constant problem.
Having recently has great problem with dates using the Filter method of a disconnected ADO recordset, I found the only reliable solution was to use the 'universls' ? date format of yyyy/mm/dd.

Having discovered this I has subsequently used it in all queries in VBA and Access without any probelms (so far!).

When dates are entered in this format in Access query designer the date is immediatly changed to the local setting format (dd/mm/yyyy for me), but in sql view in is left as is, but still gives the correct results.

So, I will continue to use this format until I find it doesn't work correctly.

This seems to be a general solution for all cases ???

MTB