Setting ALL dates to UK format

[Bogzla]

My problem is, I'm using a form to build up a query using VBA / SQL, and part of the WHERE portion of my SQL statement includes start / end dates. If I then enter dates in the UK format (dd/mm/yyyy), access will, on saving the query, reverse dd and mm if both are 12 or below. I've managed a work-around using Format(strDate, "mm/dd/yyyy") on the strings that store the dates in VBA to convert them to US (mm/dd/yyyy) format before compiling the SQL statement, but I would like to avoid using US dates completely if possible.

my PC regional settings are UK, I have UK dictionary selected within access, what I'm interested to find out is wether there is any way to force access to only use UK format dates, or is this an inherent problem?

thanks in advance for any insights

Bogzla

 

[RuralGuy]

Maybe this link will shed some light on the subject. Return Dates in US #mm/dd/yyyy# format

 

[neileg]

SQL only works with dates in US format, so it's not an Access issue. Access will try and do the conversion on the fly for you but when you build SQL satements it sometimes goes wrong. I often use a medium date format to get round the problem, eg 01-Dec-2005 instead of 01/12/2005.

 

[Bogzla]

SQL only works with dates in US format, so it's not an Access issue. Access will try and do the conversion on the fly for you but when you build SQL satements it sometimes goes wrong. I often use a medium date format to get round the problem, eg 01-Dec-2005 instead of 01/12/2005.

Thanks, I had suspicions something like this might be the case... Guess it's just another of those things we have to live with