Problem converting date stored as text to data/time format

[gddrew]

I have a problem converting text to a real date value so I can do some calculations. I have a query that brings in data from an external data source. It appears the data is stored in the external table in text format and looks like this:

20050902 15:40:41

I have tried CDate to convert the text to a date/time format, but no luck. Any ideas?

 

[KenHigg]

You may have to parse the pcs out and concatenate it back in the proper format to convert it...

 

[WayneRyan]

gd,

Cdate(Mid([ThatField], 5,2) & "/" & _
      Mid([ThatField], 7,2) & "/" & _
      Mid([ThatField], 1,4) & " " & _
      MId([ThatField], 10))

Wayne

 

[ChrisB37]

Same Problem

I have the same problem as gddrew. I don't quite understand how to implement the code above.

What is the MID for and what are the "random" numbers on the back end for?

Please clarify.

Thanks,
CB

 

[KenHigg]

Look up mid() function in vba help

 

[gddrew]

gd,

Cdate(Mid([ThatField], 5,2) & "/" & _
      Mid([ThatField], 7,2) & "/" & _
      Mid([ThatField], 1,4) & " " & _
      MId([ThatField], 10))

Wayne

This worked great, Wayne. Thanks.