A Date comparison problem

[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

 

[Emilia Maxim]

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.

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

 

[Emilia Maxim]

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.

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

 

[Hans Callesen]

Sorted Thanks very much

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

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]

Sorted Thanks very much

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

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