Any suggestions on this one...

[Guest]

Hope anyone can point me in the right direction here or to some KB explaining
how to fix this.

I've created a SQL-string in VBA which runs perfect. It consists of a couple
of parameters which are all supplied. This SQL statement is the recordsource
for my report. So far so good.

The SQL statement uses (among others) two variables entered through a
textbox on a form. These are Date values. I concatenate the variables to the
SQL-staement and the output in the immediatewindow shows exactly what it
should be.

Here's the problem. My reports are being filtered on the wrong dates.
Somehow the dates are being interpreted as US dates while they should be
European date formats.

To check the outcome i created a querydef which turns the variables around.
So 05-04-2007(EU) becomes 04-05-2007(US) format.

My Regional settings in Windows are set to Dutch (using a US keyboard
layout). All the other settings are set to Dutch.

Why is the output changed to US?

Sorry for the long post but I hope it makes any sense. Any ideas or
pinpointers greatly appreciated..

Maurice

 

[Ed Metcalfe]

Hope anyone can point me in the right direction here or to some KB
explaining
how to fix this.

I've created a SQL-string in VBA which runs perfect. It consists of a
couple
of parameters which are all supplied. This SQL statement is the
recordsource
for my report. So far so good.

The SQL statement uses (among others) two variables entered through a
textbox on a form. These are Date values. I concatenate the variables to
the
SQL-staement and the output in the immediatewindow shows exactly what it
should be.

Here's the problem. My reports are being filtered on the wrong dates.
Somehow the dates are being interpreted as US dates while they should be
European date formats.

To check the outcome i created a querydef which turns the variables
around.
So 05-04-2007(EU) becomes 04-05-2007(US) format.

My Regional settings in Windows are set to Dutch (using a US keyboard
layout). All the other settings are set to Dutch.

Why is the output changed to US?

Sorry for the long post but I hope it makes any sense. Any ideas or
pinpointers greatly appreciated..

Maurice

Maurice,

SQL always uses American date format, regardless of your regional settings.
The QBE grid in Access uses your regional settings.

You either need to format your SQL string explicitly using the Format()
function - Format(MyDate,"mm/dd/yyyy") or alternatively you can use the date
format dd-mmm-yyyy.

Ed Metcalfe.

 

[Dirk Goldgar]

In

Hope anyone can point me in the right direction here or to some KB
explaining how to fix this.

I've created a SQL-string in VBA which runs perfect. It consists of a
couple of parameters which are all supplied. This SQL statement is
the recordsource for my report. So far so good.

The SQL statement uses (among others) two variables entered through a
textbox on a form. These are Date values. I concatenate the variables
to the SQL-staement and the output in the immediatewindow shows
exactly what it should be.

Here's the problem. My reports are being filtered on the wrong dates.
Somehow the dates are being interpreted as US dates while they should
be European date formats.

To check the outcome i created a querydef which turns the variables
around. So 05-04-2007(EU) becomes 04-05-2007(US) format.

My Regional settings in Windows are set to Dutch (using a US keyboard
layout). All the other settings are set to Dutch.

Why is the output changed to US?

Sorry for the long post but I hope it makes any sense. Any ideas or
pinpointers greatly appreciated..

SQL date literals will be interpreted as US format -- month/day/year --
if they are ambiguous. You can either explicitly format them in that
format in your SQL, like this:

strSQL = "SELECT * FROM MyTable WHERE DateField = " & _
Format(dtDateValue, "\#mm/dd/yyyy\#")

or format them into an unambiguous format:

strSQL = "SELECT * FROM MyTable WHERE DateField = " & _
Format(dtDateValue, "\#yyyy-mm-dd\#")

 

[Guest]

Dick,

As mentioned to Ed, thanks for the reply. I'll format my variables first.
Answer appreciated and very clear...

Maurice

 

[Guest]

Hi Ed,

Thanks for clarifying this for me. Now I know how to get around this.

Maurice