By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,377 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Importing dates like 01JAN2003 from txt file

P: n/a
I need to import dates from a txt file that come in the format
01JAN2003. Is there a way to do this when I am importing the file
into access?

Any advice would be great!
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Jenny" <je***************@yahoo.co.uk> wrote in message
news:ec**************************@posting.google.c om...
I need to import dates from a txt file that come in the format
01JAN2003. Is there a way to do this when I am importing the file
into access?

Any advice would be great!


Generally anytime you need to "massage" imported data it is best to just import
into a temp table with all fields as text and then use an append query to move
the data to its final destination. The append query can utilize whatever
expressions and functions required to properly convert the data to its final
DataType.

In your case I couldn't find a function that would do the conversion directly
from your example string, but if you use Left(), Mid(), and Right() to add
spaces then CDate() would work.

CDate(Left([YourField], 2) & " " & Mid([YourField], 3, 3) & " " &
Right([YourField], 4))
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.