Fixed Width file dates

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
 
B

BigManT

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Importing Fixed Length File into Access 2007 1
Images are pixelated when saving to PDF 0
Fixed Width - No delimiters - ASCII Text File 7
Synchronization problems while moving folders to new email 1
Preserving trailing zeros for a fixed width file 0
Publisher is filling blank lines with data 2
|9 can't find the field '|' Error when importing a csv to Access 2 1
Import error "The specification XML failed to validate " 4

Top