By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,214 Members | 2,072 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

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

P: 4
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.
Expand|Select|Wrap|Line Numbers
  1.     Public Function DateforSQL(thisDate As Date) As String
  2. '
  3.     'This function returns a string representing the date thisDate (in UK dd/mm/yyyy format)
  4.     ' converted to US format - mm/dd/yyyy. It is necessary because MS Access (or its underlying
  5.     ' JET engine?), assumes that dates are in US format
  6.  
  7.     Dim dayStr As Integer, monthStr As Integer, yearStr As Integer
  8.  
  9.     dayStr = Day(thisDate)
  10.     ' This function returns the day of the month as an integer
  11.  
  12.     monthStr = Month(thisDate)
  13.     ' This function returns the month, as an integer
  14.  
  15.     yearStr = Year(thisDate)
  16.     ' This function returns the year, as an integer
  17.  
  18.     DateforSQL = monthStr & "/" & dayStr & "/" & yearStr
  19.  
  20.     Exit Function
  21.     End Function
  22.  
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) & "'#;"
Nov 20 '10 #1
Share this Article
Share on Google+
2 Comments


Expert 5K+
P: 8,434
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).
Sep 18 '12 #2

Expert 100+
P: 634
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
Sep 18 '12 #3