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).
Code:
=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?