CODEWITHSUNDEEP
ms-accessdate
DJDave
DJDave

Reputation: 915

date format in ms access

How can I tell Access once & for all that I am in Belfast UK and not Belfast ME? To illustrate my problem, I have created a table containing the dates of each day in 2012. If I run this query

SELECT * FROM tblListOfDays WHERE dtmDay BETWEEN #01/05/2012# AND #31/05/2012#

148 rows are returned, all the dates from 5th January to 31st May. So Access is reading one date in US format, and the other (presumably because it is has no choice) as UK format. If I use

Format("01/05/2012", "dd/MM/yyyy")

instead of

01/05/2012

then I get the answer I want, but surely there is a simpler way than having to use Format all the time? All my regional/locale settings are UK as far as I can check.

Upvotes: 1

Views: 5474

Answers (2)

Ould Abba
Ould Abba

Reputation: 843

as petr-abdulin said you can use 

Format(expression, format)

So your code becam : 

SELECT * FROM tblListOfDays WHERE Format(dtmDay, "dd-mm-yyyy") BETWEEN 
     Format("01/05/2012", "dd-mm-yyyy") AND Format("31/05/2012", "dd-mm-yyyy")

and dont forget to use format while inserting also.

and here you can find more info about this function.

Upvotes: 1

pabdulin
pabdulin

Reputation: 35219

AFAIK there is no other way than to specify date format explicitly using Format function.

Upvotes: 1

Related Questions