Strange problem with date formats

[Trevor Bourne]

With my system date format set to English(New Zealand)a
recordset search for the current November date worked
fine. However, now that we have moved into December the
same search does not find the current December date
unless I change the system date to English(United States).

I also noticed that with English(New Zealand) selected,
the Calendar control is now defaulting to 12 Feb 2003
instead of 2 December 2003.

This has me baffled. Any suggestions please ?

 

[Nikos Yannacopoulos]

Trevor,

You mention recordset, so I guess you're referring to
working with VBA code? If that's the case, then you gotta
note that to VBA the only date format is US (mm/dd/yy), so
anything else is misinterpreted!
I've had this problem myself, and found that a safe way to
deal with it regardless of each user's Windows settings is
to pass the date to the SQL/recordset string after playing
around with it, like:
mydate = month(date) & "/" & day(date & "/" & year(date)

Nikos

 

[John Vinson]

With my system date format set to English(New Zealand)a
recordset search for the current November date worked
fine. However, now that we have moved into December the
same search does not find the current December date
unless I change the system date to English(United States).

I also noticed that with English(New Zealand) selected,
the Calendar control is now defaulting to 12 Feb 2003
instead of 2 December 2003.

This has me baffled. Any suggestions please ?

Literal dates in SQL queries or in VBA MUST - no options, no choice! -
be in either an unambiguous format such as 2-Dec-2003 or in American
12/2/2003 format. The user's regional settings don't matter - the
query

SELECT * FROM mytable WHERE datefield = #3/12/2003#

will retrieve data from February 12, not from tomorrow.

 

[Guest]

Nikos

Many thanks for your response. Yes, I am using VBA to loop
through a recordset looking for BookingDate = Date(). I
will take up your suggestion to overcome the problem.

Trevor

 

[Guest]

John

Thanks for your response. This confirms what Nikos has
written in his response. With this knowledge I should now
be able to tackle the problem.

Trevor

 

[Pat Garard]

Hi Trev,

The 'problem' lies with SQL.

SQL is an ANSI Standard for communicating with
RDBMSs - nothing to do with Microsoft or Access.

In that standard, the date format is MM/DD/YYYY.

This can be confusing even when VBA is not being
used.

In Query Design View, setting a criterion
'SomeDate =--/--/----'
gives rise to much confusion where the 'local' date
format is 'dd/mm/yyyy' - Access itself sometimes gets
it wrong, and the user needs to examine the SQL
carefully - even in Australia.
--
Regards,
Pat Garard
Australia

Anne & Pat Garard.
apgarardATbigpondDOTnetDOTau
_______________________________________________