Question importing unformatted .txt file date field into MS Access

stirlsa

New member
Local time
Today, 08:06
Joined
Apr 10, 2009
Messages
2
I am trying to import a 40,000 row text file in MS Access. All is fine except the most important field which is a date field YMD with a 4-digit year, leading zeroes and no / to separate. Example: 20081209
The date field import into ACCESS yields a 'type conversion failure' and none of the dates are imported into ACCESS. How can I get ACCESS to accept this as a date field?

I have googled it and only find that it would accept it if I went into the text file and added the slashes into the text file before import. Example: 2008/12/09
I do not know how to do that in an automated fashion.
 
Don't import as a date field; import it as a text field and fix it later with an update query.
 
Hi -

If your formatting is consistent, your update query might incorporate this:

Code:
x = "20081209"
y = dateserial(Left(x, 4), mid(x, 5,2), right(x,2))

? y
12/9/2008

to prove y is actually in datetime data format
? cdbl(y)
 39791

HTH - Bob
 

Users who are viewing this thread

Back
Top Bottom