International Dates in Access SQL (VBA or Direct Query)

After a day of googling, binging, shouting and screaming at an inorganic yet most likely demonic creation that is the PC, I think I've finally managed to sort this baby out.

The Environment

Date in a table is stored using the UK date system, i.e. dd/mm/yyyy. System is also set for the UK date/time and locale.

The Problem

In VBA, I was using Dlookup to retrieve an ID associated to a specific date. It sometimes returned the wrong result (sometimes Null). This was inside a loop and looped through a set of dates inside a table.

Attempted Solutions

At first I thought its a flaw in VBA. So i tried Cdate , CVdate, converting to string, converting to date, comparing strings and so on and so forth. Spent half a day playing with VBA trying to get the right date.

Then (i dont know why i waited this long to try this...live and learn!), I decided to try a query in SQL using an explicitly declared date. It worked. So back to VBA.

After another hour or so...I had narrowed it down to errors starting when the month changed a carrying on thereafter. Again I decided to check SQL and Voila! Wrong Result. Changing to the american date system gave the right result. So obviously even though my system settings and everything else said UK date format not to mention access using the right format everywhere else, when it came to an SQL query involving dates, it only used the american method.

Then came further web trawling to try and fix this issue...

Working Solution

The following method worked for me.

Suppose you have the following code:

...

dim tmpdate as date 'this is the date I have

dim tmpstr as string 'this is the string i want a value returned to

tmpdate ="15/09/2007" 'The code actually looked it up and got the right answer

tmpstr = Dlookup("field","tablename","[DatetoUse]=" & tmpdate)

...

It gave tmpstr = Null even though it shouldnt. However if I modified the criteria bit in Dlookup using the Format() function, it worked ok. My new line was

tmpstr = Dlookup("field","tablename","[DatetoUse]=" & Format(tmpdate,"mm/dd/yyyy"))

Bizzare. At least now it works correctly!