Date/time US <> EU

[djemmers]

Hello my sql/access friends

I have a table with a date/time field named “teLezenOp”

and I use the following line in VBA:
SELECT * FROM tblBericht WHERE (teLezenOp<=#" & MediumDate(Now, Now) & "# or teLezenOp is Null ) ORDER BY datum

(I use the european notation dd-mm-yyyy)
Here is what goes wrong: yesterday 28-2-2006 there was no problem
But today 1-3-2006 I get an error:
Syntax error in querry-expression: telezenOp<=#1-mrt-2006# or telezenOp is Null

If I look in my acces DB telezenOp = 20-2-2006 so there is a difference in notation? 20-2-2006<= 1-mrt-2006 I don't think this is the problem because yesterday it worked with the same code.

now I've just read the following in one of the threads on the forum:
"In VBA code and query SQL statements, the date between the # signs must be put in US format. So #4/1/2005# means April 1, 2005."

I guess this is the reason of my problem:
I compare a EU notation with a #EU#(so that is incorrectly read as a US notation)
is there an easy way to switch between EU and US notations in VBA/sql statements?
I guess there is but I can't find it...

tnx for any (hopfully rediculously simple) tips/tricks/sollutions

djemmers

 

[EMP]

Try this:-

SELECT * FROM tblBericht WHERE (teLezenOp<=#" & Format(Date,"m/d/yyyy") & "# or teLezenOp is Null ) ORDER BY datum

^

 

[Bat17]

You could try
SELECT * FROM tblBericht WHERE (teLezenOp<=#" & Format(Date(),"MM/DD/YYYY") & "# or teLezenOp is Null ) ORDER BY datum

Which should give the SQL the date in US format.

Peter

 

[djemmers]

tnx for the quick reply

but I don't think ik will work:
teLezenOp<=#" & Format(Date(),"MM/DD/YYYY")
because this will give EU<=US so will give wrong results.
so it should be something like this?
Format(teLezenOp,"MM/DD/YYYY")<=#" & Format(Date(),"MM/DD/YYYY")

problem is that I can't check it now,
I am at home now..
(live in belgium)

greetz

djemmers

 

[Bat17]

teLezenOp stores the date as a decimal number of days from a start point. it just displays the date in the format you tell it to. When you wrap a date in #'s access converts it back to a number but it uses the US date format to do it.

the way we have given you should work OK

Peter

 

[djemmers]

works great

tnx!