Problems using Format & Cdate to convert text string to Date format

[thewilldog]

Hello, I've reviewed the archives here to address the issue, but I'm still running into problems. I've got a table field [CAS-ACC-OPEN-DATE] populated with the record date in text "YYYYMMDD"

To convert it into a recognizable date format, I've done the following:

Query 1: References Source Table; Isolates Year, Day; creates MMDD field
Acc Open Year: Left([CAS-ACC-OPEN-DATE],4)
Acc Open Day: Right([CAS-ACC-OPEN-DATE],2)
MMDD: Right([CAS-ACC-OPEN-DATE],4)

Query 2: References Query 1; Isolates Month
Acc Open Month: Left([MMDD],2)

Query 3: References Query 1 & 2; Concatenates Year, Month, Day, into MM/DD/YYYY
Acc Open Date: [Acc Open Month] & "/" & [Acc Open Day] & "/" & [Acc Open Year]

So now I have the date in MM/DD/YYYY format, but it's still stored as text. It needs to be in date format so I can do a calc to figure out how long an account has been open. I've tried nesting the expression in query 3 within Format-Cdate to convert it to a date format, but all it did was return errors in the table. See below:

ACC_OPEN_DATE: Format(CDate([ACC_OPEN_MONTH] & [ACC_OPEN_DAY] & [ACC_OPEN_YEAR]),"mm/dd/yyyy")

Can someone tell me what I'm doing wrong?

[Rabbit]

You forgot the "/"

[MMcCarthy]

ACC_OPEN_DATE: Format(CDate([ACC_OPEN_MONTH] & "/" & [ACC_OPEN_DAY] & "/" & [ACC_OPEN_YEAR]),"mm/dd/yyyy")