A Date comparison problem

  • Thread starter Thread starter Hans Callesen
  • Start date Start date
H

Hans Callesen

Hi
I have a derived field from 2 controls ("22 01 2003"[from a date/Time column
of format Longdate] + "21:41"[from a date/Time column of format ShortTime] =
"22 01 2003 21:41"[). The derived field is stored by a variable (VarY) of
type Variant.
I use VarY in a Dlookup statement where VarY is between 2 values in a lookup
table. Both fields are Date/Time type with format General date (e.g.:
"22/01/2003 09:00:00" and "22/01/2003 18:59:00")).
This works 100% where the date format is obvious,as above [dd/MM/yyyy] BUT
for a less obvious date like "05/10/2003 09:00:00" (Read: 05 October 2003
09:00) the date comparison assumes the format MM/dd/yyyy and returns the
incorrect value.
The machine regional settings are set to dd/MM/yyyy for both short & long
date.
I've tried this in a query - same results from same table. Any tips here
please?
thks
 
Hans Callesen said:
I have a derived field from 2 controls ("22 01 2003"[from a date/Time column
of format Longdate] + "21:41"[from a date/Time column of format ShortTime] =
"22 01 2003 21:41"[). The derived field is stored by a variable (VarY) of
type Variant.
I use VarY in a Dlookup statement where VarY is between 2 values in a lookup
table. Both fields are Date/Time type with format General date (e.g.:
"22/01/2003 09:00:00" and "22/01/2003 18:59:00")).
This works 100% where the date format is obvious,as above [dd/MM/yyyy] BUT
for a less obvious date like "05/10/2003 09:00:00" (Read: 05 October 2003
09:00) the date comparison assumes the format MM/dd/yyyy and returns the
incorrect value.
The machine regional settings are set to dd/MM/yyyy for both short & long
date.
Click to expand...

Hans,

SQL uses (and expects) the ANSI syntax regardless of local settings,
and this is mm/dd/yyyy (you cannot have A Jet engine for each
localized version). When you create a query in design view in the
grid, Access takes care of the conversion (you can check this by
switching between design view and SQL view). So when putting together
date criteria in code, you always have to format the date accordingly:

Format(varY, "mm/dd/yyyy hh:nn:ss")

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Sorted Thanks very much
Andy Cole said:
Hans

When you're dealing with SQL it's important to reformat dates into the US
format. See the following link for how to do this;

http://www.mvps.org/access/datetime/date0005.htm

HTH

Andy

Hans Callesen said:
Hi
I have a derived field from 2 controls ("22 01 2003"[from a date/Time column
of format Longdate] + "21:41"[from a date/Time column of format
Click to expand...
ShortTime]
=
"22 01 2003 21:41"[). The derived field is stored by a variable (VarY) of
type Variant.
I use VarY in a Dlookup statement where VarY is between 2 values in a lookup
table. Both fields are Date/Time type with format General date (e.g.:
"22/01/2003 09:00:00" and "22/01/2003 18:59:00")).
This works 100% where the date format is obvious,as above [dd/MM/yyyy] BUT
for a less obvious date like "05/10/2003 09:00:00" (Read: 05 October 2003
09:00) the date comparison assumes the format MM/dd/yyyy and returns the
incorrect value.
The machine regional settings are set to dd/MM/yyyy for both short & long
date.
I've tried this in a query - same results from same table. Any tips here
please?
thks
Click to expand...
Click to expand...
 
Back
Top