471,120 Members | 1,454 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 471,120 developers and data experts.

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

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
  7.     Dim dayStr As Integer, monthStr As Integer, yearStr As Integer
  9.     dayStr = Day(thisDate)
  10.     ' This function returns the day of the month as an integer
  12.     monthStr = Month(thisDate)
  13.     ' This function returns the month, as an integer
  15.     yearStr = Year(thisDate)
  16.     ' This function returns the year, as an integer
  18.     DateforSQL = monthStr & "/" & dayStr & "/" & yearStr
  20.     Exit Function
  21.     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) & "'#;"
Nov 20 '10 #1
2 2960
8,435 Expert 8TB
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
637 Expert 512MB
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 ???

Sep 18 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.