CDate Function is being reformatted and creates an Error

user_10023833 0
Viewed 48

I have a select query which has a date stored as string e.g. 20211231 (Dec 31, 2021). I am trying to use the CDate function to convert the string. If I enter the following in the SQL view

SELECT CDate(Format([XLCustOrdDtl]![DATE_ADDED],"####/##/##")) AS DteAdded
FROM XLCustOrdDtl;

The result are as expected, however when I go back to Design view and run the query, Access reformats the string to

SELECT CDate(Format([XLCustOrdDtl]![DATE_ADDED],"#\/#\/#")) AS DteAdded
FROM XLCustOrdDtl;

This results in #Error

Any thoughts on why this is happening and how I might correct the issue?

data-conversion ms-access
2 Answers

As your date is text, let Format know this and use @.

Also be aware, that to Format a slash is not a slash but the local date separator. To force a literal slash, escape it with a backslash:

SELECT CDate(Format([XLCustOrdDtl]![DATE_ADDED],"@@@@\/@@\/@@")) AS DteAdded
FROM XLCustOrdDtl;
user_35272970

Apparently, lack of hyphen or slash punctuation in string means Access cannot recognize date parts and # is a date delimiter character and not valid as date format placeholder.

Assuming there are no Null fields and there are always 8 digits including placeholder zeroes, consider:

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

user_76071900
Related