Non US Date Field Problem

[Richard Myers]

Hello

My regional settings are UK English. Im looping through a recordset using
some of the values as criteria to run an query in each iteration. My
problem is that Access is interpreting the date differently for no apparent
reason. Below i have the basics of the loop. Note that i haven;t included
full strSql for simplicity
So for example when i run the query where

!MovingTrendStart = 01/01/05
!PeriodStart = 01/04/05

im getting back results only for records that have a date between 1st and
the 4th of Jan 2005. Even though the debug.Print Month statement return 1 &
4 respectively? Whats the deal?

Richard

Do While Not .EOF

......WHERE " & "PeriodStart Between #" &
DateSerial(Year(!movingtrendstart), Month(!movingtrendstart), 1) & "# AND
#" & DateSerial(Year(!periodstart), Month(!periodstart), 1) & "#"
DoCmd.RunSql strSql

Debug.Print Month(!movingtrendstart)
Debug.Print Month(!periodstart)

.MoveNext
Loop

 

[Allen Browne]

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article comes from 12 years experience working with dates in a dd/mm/yy
country, and explains how to avoid the 3 common traps.

 

[Richard Myers]

Hi Allen,

Thanks alot for that. It has proved really helpful. As per your article it
seems Microsoft has gotten just a little bit too cute with the whole date
thing in Access resulting in an inconsistent developer experience.
Knowledge is power however and for that i thank you.

As an aside Im wondering how much you might know about what happens when
passing sql strings from another programming interface. I suppose i could
test this myself but if you have already been through it then I'd prefer
not to have to repeat the process.

So for instance if im using an Access backend for a c#/vb.net application,
I haven't noticed these same problems. If i send a query string to
Access/Jet from a dotNet app then it doesn;t *swap* dates on me. So does
the Sql datae swapping only occur when entering into the Access interface
itself; such as QBE and not if we simply pass a string into Jet from
another frontend?

Cheers
Richard

 

[Allen Browne]

Yes, the twirling-the-numbers to fit a date is a "feature" of the Access
interface. Fortunately, literal dates in a SQL string are handled much more
consistently.