SQL and design mode are different

[Jean-Paul]

I wrote some code to get data between 2 dates:

sql = "SELECT Sum(Uren.minuten) AS SomVanUur FROM Uren WHERE
Uren.Datum > #" & Format(Forms![Uur_overzicht]!Start_dat, "DD/MM/YYYY")
& "# And Uren.Datum < #" & Format(Forms![Uur_overzicht]!Stop_dat,
"DD/MM/YYYY") & "# ;"

The result is something like:

SELECT Sum(Uren.minuten) AS SomVanUur FROM Uren WHERE Uren.Datum >
#01/05/2008# And Uren.Datum < #30/05/2008# ;

When I put this in a query and got to the design
mode there is some tranformation of the first date into 05/01/2008
while the second date remains 30/05/2008

What am I doing wrong here.
Thanks

 

[Jean-Paul]

but, what to do if I stored all dates in my table in dd/mm/yyyy format?
JP

Jet SQL uses U.S. date format (mm/dd/yyyy). If you use a different date
format for your coding, you'll cause confusion.

Chris
Microsoft MVP

I wrote some code to get data between 2 dates:

sql = "SELECT Sum(Uren.minuten) AS SomVanUur FROM Uren WHERE
Uren.Datum > #" & Format(Forms![Uur_overzicht]!Start_dat, "DD/MM/YYYY")
& "# And Uren.Datum < #" & Format(Forms![Uur_overzicht]!Stop_dat,
"DD/MM/YYYY") & "# ;"

The result is something like:

SELECT Sum(Uren.minuten) AS SomVanUur FROM Uren WHERE Uren.Datum >
#01/05/2008# And Uren.Datum < #30/05/2008# ;

When I put this in a query and got to the design
mode there is some tranformation of the first date into 05/01/2008
while the second date remains 30/05/2008

What am I doing wrong here.
Thanks

 

[John Spencer]

Try using ANSI standard for the date format. A problem for non-US
systems is that JET SQL expects the date to be in US format of
mm/dd/yyyy. It can handle dates that are unambiguous such as
#30/05/2008# because the programmers have it check and if the "month"
position is greater than 12 then the code makes this the "day" and
treats the next two numbers as if they were the month.

I would rewrite the code to use a yyyy/mm/dd format. This should always
be interpreted correctly.

sql = "SELECT Sum(Uren.minuten) AS SomVanUur FROM Uren WHERE
Uren.Datum > #" & Format(Forms![Uur_overzicht]!Start_dat, "yyyy/mm/dd")
& "# And Uren.Datum < #" & Format(Forms![Uur_overzicht]!Stop_dat,
"yyyy/mm/dd") & "# ;"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Rick Brandt]

but, what to do if I stored all dates in my table in dd/mm/yyyy
format? JP

You didn't. In a DateTime field format is not stored at all. It only
affects what you see.