How do I convert imported text-delimited date to usable Access data? 1

[MikeFL]

I have a LARGE downloaded AS400 *.asc and a *.csv file that has a date field in it that looks like this 19931001, but I want to look like this 10/01/1993 and be usable in Access programs.

I’ve tried to change the format of the field after import to a date type and that did not work. I then tried changing the format before the import to a date type field and that did not work. The filed came in the Access file as a long integer type.

Does anyone have any suggestions?

 

[GoDawgs]

Try this:

CDate(Mid([test],5,2) & "/" & Right([test],2) & "/" & Left([test],4))

Where "[test]" is the name of the field containing "19931001"...this assumes that there's always a 4 digit year, 2 digit month, and 2 digit day. Hope that helps.

Kevin

 

[MikeFL]

Kevin godawga (MIS) your suggestion worked! Thanks!