473,245 Members | 1,519 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,245 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
  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
2 3192
Killer42
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
MikeTheBike
639 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 ???

MTB
Sep 18 '12 #3

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

Similar topics

3
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - Why does simple decimal arithmetic give strange results?...
2
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - Why does simple decimal arithmetic give strange results?...
1
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - Why does simple decimal arithmetic give strange results?...
2
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - Why does simple decimal arithmetic give strange results?...
1
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - Why does simple decimal arithmetic give strange results?...
0
by: sofiashaharom | last post by:
I've connected my program (developed in VB 2008) to a microsoft access (2003) database.One of the columns stored in the database is the Start_Date. The purpose of my program is to calculate the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.