Convert a Text field to Date/Time

[Guest]

I have a text file that I am importing into Access. All fields populate
correctly except for the date fields. They import as text "06082007" and
need to be a date filed like 06/08/2007. I have tried changing the field
property before and after import, appending the text filed to a new Date/Time
Field with no success. Is there an eaiser way than using left( ), right( ),
mid( )?

Any help would be appreciated....Thanks!

 

[Rick Brandt]

I have a text file that I am importing into Access. All fields
populate correctly except for the date fields. They import as text
"06082007" and need to be a date filed like 06/08/2007. I have tried
changing the field property before and after import, appending the
text filed to a new Date/Time Field with no success. Is there an
eaiser way than using left( ), right( ), mid( )?

Any help would be appreciated....Thanks!

CDate(Format(CLng([YourData]),"00/00/0000"))

That will be potentially problematic as CDate relies on the user's regional
settings in Windows to decide if the first two numbers are the month or the
day (when both are 12 or less). DateSerial would eliminate that problem but
then you do have to parse the string up a bit more...

DateSerial(CInt(Right([YourData],2)), CInt(Left([YourData], 2)),
CInt(Mid([YourData],3,2)))