CDate Function is being reformatted and creates an Error-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

Apparently, lack of hyphen or slash punctuation in the string means Access cannot recognize date parts and that is not really a valid Format template. It is probably a good Input Mask.

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))