Access 2007 External Data Import will not recognize yyyymmdd date

[catkin63]

I have been trying to import a tab-delimited .txt file that has the dates in
a non-delimited format of yyyymmdd. I have told the wizard that the date is
in four digit years with leading zeros in YMD order with no date delimiter
and using the date datatype. I have verified that the raw data has no
additional spaces. This exact same import process worked perfectly for three
years in Access 2003, but will not work in Access 2007. One suggestion was to
go into Access Options : Advanced : General : Use four-digit year formatting
and check either This database or All databases. I chose all ... to no
effect. I have looked at some of the Q&A, but most of the answers do not
address the wizard, which has no option for CDate or DateSerial. Please help.
Thank you.

 

[catkin63]

Please ignore this, as I finally found my answer, after I had posted. Thank
you Klatuu for a succinct solution and Benjy for more explanation.

 

[Jeff Boyce]

Can you confirm that the data you're importing is still "text"?

Can you "link" to the data instead of importing it?

Can you create a query against the data (either linked or imported) and add
a new field something like:

NewField: CDate([YourImportedDateField])

If that conversion doesn't work, can you add the new field and use the
DateSerial(), and the Mid() functions to generate an Access Date/Time value.

Since many tab-delimited files are not well normalized, you could use this
opportunity to parse your input data into your more permanent (and hopefully
better normalized) Access tables.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[catkin63]

Thank you, but I don't want a linked query, I need to import it. I found out
that the main problem is in Access 2007. The Import Wizard cannot recognize
that arrangement for a date for some reason. I will be having to import,
query, append or create to another table using the CDate function, then
manipulate my data. But thank you anyway.

Can you confirm that the data you're importing is still "text"?

Can you "link" to the data instead of importing it?

Can you create a query against the data (either linked or imported) and add
a new field something like:

NewField: CDate([YourImportedDateField])

If that conversion doesn't work, can you add the new field and use the
DateSerial(), and the Mid() functions to generate an Access Date/Time value.

Since many tab-delimited files are not well normalized, you could use this
opportunity to parse your input data into your more permanent (and hopefully
better normalized) Access tables.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have been trying to import a tab-delimited .txt file that has the dates
in
a non-delimited format of yyyymmdd. I have told the wizard that the date
is
in four digit years with leading zeros in YMD order with no date delimiter
and using the date datatype. I have verified that the raw data has no
additional spaces. This exact same import process worked perfectly for
three
years in Access 2003, but will not work in Access 2007. One suggestion was
to
go into Access Options : Advanced : General : Use four-digit year
formatting
and check either This database or All databases. I chose all ... to no
effect. I have looked at some of the Q&A, but most of the answers do not
address the wizard, which has no option for CDate or DateSerial. Please
help.
Thank you.

 

[dejames]

since you found your answer, why not share it with the group...