Changing Date format when Importing TXT file

[Guest]

I'm new to access and could use some help. I'm importing data into an access
table. the TXT file displays the date as 032807 however when it is imported
into access I want it to display in the table as 03/28/07. I have the Imput
Mask set in the table as "mm"/"dd"/"yy" however I get an error when I import
the data. Any help is appreciated, thank you.

 

[Rick Brandt]

I'm new to access and could use some help. I'm importing data into
an access table. the TXT file displays the date as 032807 however
when it is imported into access I want it to display in the table as
03/28/07. I have the Imput Mask set in the table as "mm"/"dd"/"yy"
however I get an error when I import the data. Any help is
appreciated, thank you.

032807 is not a date and cannot be inserted into a date field directly no
matter what you do with formatting and input masks neither of which have a
thing to do with importing data.

You can either...

Link to the text file and then use an Append query based on the link to
insert the data into your destination table. In that append query you can
use...

Format(YourField,"00/00/00")

on that field and then it should be accepted in the date field.

OR

You can import first into a holding table where that field is defined as
text and then append from that table into the final one using the same
method outlined above.

 

[Pat Hartman \(MVP\)]

The import wizard gives you the option of specifying the format of the
source date fields and mdy with no separators is one of the options. Press
the advanced button to see your choices. Then save the import spec. Once
saved, you can reference the spec in the TransferText Method/Action so you
only have to do this once.