Help with dates?

[Guest]

Using Access2003
this function uses the date format 02 febuary 2007 and returns the wrong
results

?Format(DSum("[TotalPriceIncludingVAT]", "tblExpenses", "[ExpensesDate] >=
#01/02/07# and [ExpensesDate] <= #28/02/07#"), "Currency")
£1,244.99

where this function uses the date format febuary 02 2007 and returns the
correct format

?Format(DSum("[TotalPriceIncludingVAT]", "tblExpenses", "[ExpensesDate] >=
#02/01/07# and [ExpensesDate] <= #02/28/07#"), "Currency")
£1,044.99

i would like to use the uk format 01 febuary 2006. Can anyone explain what
is going on please?

 

[RoyVidar]

Using Access2003
this function uses the date format 02 febuary 2007 and returns the
wrong results

?Format(DSum("[TotalPriceIncludingVAT]", "tblExpenses",
"[ExpensesDate] >= #01/02/07# and [ExpensesDate] <= #28/02/07#"),
"Currency") £1,244.99

where this function uses the date format febuary 02 2007 and returns
the correct format

?Format(DSum("[TotalPriceIncludingVAT]", "tblExpenses",
"[ExpensesDate] >= #02/01/07# and [ExpensesDate] <= #02/28/07#"),
"Currency") £1,044.99

i would like to use the uk format 01 febuary 2006. Can anyone explain
what is going on please?

Have a look at Allen Brownes explanations here
http://allenbrowne.com/ser-36.html

When you feed the Jet engine a string containing field names and
date litterals, you need to use an unambiguous format.

Unambiguous date formats, can be US format (shown in Allen Brownes
article) or for instance ISO 8601 "yyyy-mm-dd".

 

[Douglas J. Steele]

Using Access2003
this function uses the date format 02 febuary 2007 and returns the
wrong results

?Format(DSum("[TotalPriceIncludingVAT]", "tblExpenses",
"[ExpensesDate] >= #01/02/07# and [ExpensesDate] <= #28/02/07#"),
"Currency") £1,244.99

where this function uses the date format febuary 02 2007 and returns
the correct format

?Format(DSum("[TotalPriceIncludingVAT]", "tblExpenses",
"[ExpensesDate] >= #02/01/07# and [ExpensesDate] <= #02/28/07#"),
"Currency") £1,044.99

i would like to use the uk format 01 febuary 2006. Can anyone explain
what is going on please?

Have a look at Allen Brownes explanations here
http://allenbrowne.com/ser-36.html

When you feed the Jet engine a string containing field names and
date litterals, you need to use an unambiguous format.

Unambiguous date formats, can be US format (shown in Allen Brownes
article) or for instance ISO 8601 "yyyy-mm-dd".

<picky>
The so-called "US format" is definitely ambiguous. It's just that Microsoft,
being US-based, allowed it as a default.
</picky>