Advice on dealing with Access' ## expecting an american date.

[abodi]

Hi Everyone.

So I'm in Australia, and we use DD/MM/YYYY.
Our local system settings specify that formating and it works fine.

However on a form I'm using a dcount to count the number of entries that exist on that date and has the status approved.
This is the code I used (Thanks June7).

=DCount("*","[tblLeaveRequests]","Status='Approved' And #" & CDate([vDate1]) & "# BETWEEN [LeaveStartDate] AND [LeaveEndDate]")

This is where the problem occurs though. The above code doesn't work without # #, but the # also then expects the date to be in an american format and is screwing up the counting.





As you can see 04/08 - 07/08 it's counts zero. that because it is looking for the 8th Feb. you'll see from the 13/08 it's counting and that because access goes "oh damn, it must be the other way around" and then counts correctly.

From a usability point of view when entering dates into the DB users need to be in DD/MM/YYYY format, just to make it fool proof/user friendly.

I've done a bit of googling, but i've not found something that kickstarts my brain to a solution. so how can I work around/with this issue?

[pbaldy]

From a fellow Aussie:

http://allenbrowne.com/ser-36.html#Format

[abodi]

Pbaldy, i've read that at least twice at this point, but I really can't parse that into a solution.

[John_G]

Hi -

Instead of using CDate, use the Format function:

=DCount("*","[tblLeaveRequests]","Status='Approved' And #" & format([vDate1],"mm/dd/yyyy") & "# BETWEEN [LeaveStartDate] AND [LeaveEndDate]")

to put the date in the required format.

John

[abodi]

Thank you so much John, that was a revelation.

I decided to get out of the building for lunch to try clear the cobwebs. I think i thought of another solution, but yours is much more elegant and simple!

Thank you pbaldy, combined with Johns reply, i now understand the link you provided.