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