By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

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

P: 1
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?
Mar 28 '07 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,327
You forgot the "/"
Mar 28 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
ACC_OPEN_DATE: Format(CDate([ACC_OPEN_MONTH] & "/" & [ACC_OPEN_DAY] & "/" & [ACC_OPEN_YEAR]),"mm/dd/yyyy")
Mar 29 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.