Convert string to date in Access

Question

I transfer data between tables in Access 2003. In the old table, the date was saved as a text field in the format YYYYMMDD.

I want to save the field as a date in a new table. I tried using CDate()SQL in my statement, but it just shows up as #Errorin the results.

What am I doing wrong?

Answer 1

eg. cdate(format("20091231", "####/##/##"))

So in your case it will be

SELECT cdate(format(mystringFieldThatIsInYYYYMMDDFormat, "####/##/##"))   
FROM myData