Importing dates like 01JAN2003 from txt file

  • Thread starter Thread starter Jenny
  • Start date Start date
J

Jenny

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!
 
Jenny said:
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!
Click to expand...

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))
 
Back
Top