Convert text YYYYMMDD to date mmddyyyy 1

[CarpalT]

I have a field downloaded from another db that is a text field for a date in yyyymmdd order. I am trying to get it into Access date format so I can create a cross tab and a graph with it further down the line.
I used a make table query to convert the date using this expression
Dateval: (Val(Right([tdate],4)) & Val(Left([tdate],4)))

However that makes a number field and I can't change it to date in the table without losing the data.
If make the expression
Dateval:CDate((Val(Right([tdate],4)) & Val(Left[tdate],4))))
in the query, the query returns #error.

 

[skyel]

hmmm... nothing in my new column... sigh...

 

[GoDawgs]

You know what, I've been reading your query wrong the entire time I think...AppDate and FirstApp are actual fields in the PRSUData table, not variables in the code. If that's true then the fields should be included in the sqlstring.

SQLString = "UPDATE __test SET [__test].FIRSTAPPDATE = Left([appdate],4) & "/" & Mid([appdate],5,2) & "/" & Right([appdate],2) & " " & [firstapp];"

Then do:

CurrentDb.Execute SQLString

Make sure you change your variable to SQLString...just to be safe. Hope that finally helps...

 

[GoDawgs]

This came out wrong when it got posted...use apostrophes (single quotes) instead of double quotes everywhere except for the very beginning and the very end.

 

[Travlnbard]

Tranman,
Thanks so much for your assistance. It works great! (And since I have had to it for 18 date field this posting has been a great help.)
Cathy
PS Next time I will remember to mark the post for e-mail notification so that I know when someone replies (duh).