Fixed Width file dates

  • Thread starter Thread starter Chris K
  • Start date Start date
C

Chris K

I have worked with Access for years, but still cannot find
a way to import a date field from a Fixed width file into
an Access table with a date format. I have to import the
file into Excel which successfully puts the field into
date format export to Access.
I am pulling the file through IBM Client Access Express if
that helps.
Any suggestions?
thanks
Chris
 
Here is the deal Chris. Access cannot do this as directly as you might like, but I will show a way to do it.
1st, import the 8 characters that make up the date. Let me assume you have it in the 20040514 format in a field called dtmDate. Then add the table to a query and parse out the string like this.
MyDateFormattedField: DateValue(mid(dtmDate,5,2) & "/" & right(dtmDate,2) & "/" & Left(dtmDate,4)
MyDateFormattedField now has a recognized date data type. You can append it or use it to update a date field as you wish.

-BigManT
 
Back
Top