Convert a string to a date in Access

Question

I'm migrating data between tables in Access 2003. In the old table, the date was stored as a text field in the format YYYYMMDD.

I want to store the field as a datetime in the new table. I've tried using CDate() in my SQL statement, but it just displays as #Error in the results.

What am I doing wrong?

Answer 1

e.g. cdate(format("20091231", "####/##/##"))

So, in your case it will be

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