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

Rabia

In Access, Date() is a function which accepts no arguments. Beyond that I don't understand what you want to accomplish. When your DateField contains the text, "20150821", what do you want to replace it with? It looks like your expression would give you "20150821" back again. – Whidah

I think you want the DateSerial() function. – Venison

@GordThompson Thank you. It worked :) @ HansUP the field is designated for date but it comes out as numbers. So I wanted to properly format it before exporting the data to Excel. – Rabia

Answer 1

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

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

Venison

Answer 2

I recommend using CDate function and type in the appropriate format for date

NewDate: CDate(Format([TextField], "0000-00-00"))

Dashboard

could you provide a bit of detail to make this answer better at helping future readers? and format the code by highlighting it and hitting Ctrl+K – Anticyclone

Just used this - worked perfectly and is simpler than the one above so +1 (and I also modified the format as suggested) – Stoical

This code worked like charm. Thank you for sharing this insight. Some explanation to the code on how this works would add clarity. – Gershon