Date reversal in WHERE clause

[diwin]

I created a database on my computer and moved it to the data entry clerks. Our system date formats are reversed. Mine is mm/d/yyyyy, hers is dd/mm/yyyy.

I didn't think this should matter, but it seems to wreaking havoc with my search SQL string. This string is built based on control values on the search form...

[blue]"SELECT * FROM qryDonationSearchOutput WHERE Int(donationEntryDate) >= #01/02/2006# AND Int(donationEntryDate) < #09/02/2006# ORDER BY donationID DESC"[/blue]

I read... "date >= first/Feb/2006 AND date < ninth/Feb/2006"

but it is actually pulling donations between (here comes the reversal) second/Jan/2006 AND second/Sept/2006.


I displayed one of the dates as 'Long Date' with the following...

[blue]Debug.Print sSQLEndDate & vbCrLf & Format(Int(Me.txtSearchDateEnd), "Long date")[/blue]

...displays the following...

Int(donationEntryDate) < #09/02/2006#
February 9, 2006

I checked the tables and they show the date right. So, where on earth is the screwup happening????

Daniel Dillon
o (<--- brain shown at actual size.)

 

[diwin]

Found it.

My short and long date formats in the regional settings are in the same ORDER...

m/d/y

Hers were not similar...

short = d/m/y
long = m/d/y

...and somewhere in the code, I didn't account for short or long. I found it easier to switch her long to the same as the short.

Daniel Dillon
o (<--- brain shown at actual size.)

 

[Golom]

It matters quite a bit. Internally Access uses mm/dd/yyyy for dates that are ambiguous (i.e. day <= 12), regardless of the localle settings. You should set her system localle date format to mm/dd/yyyy OR use some non-ambiguous representation of dates such as yyyy-mm-dd.