Convert yyyymmdd number or string to true Date value in MS Access

Question

I have a query that has a date field in this format (yyyymmdd) just numbers. I am reformatting the the field by creating a calculated field using the following formula:

[DateField] = Date(Left( [DateField] ,4),Mid( [DateField] ,5,2),Right( [DateField] ,2))

I keep getting a message saying my formula contains wrong number of arguments!! This formula always worked in Excel.

Please advise

Cheers

Answer 1

In Access you'll need to use the DateSerial() function:

DateSerial(Left([DateField], 4), Mid([DateField], 5, 2), Right([DateField], 2))