Access 2007 External Data Import will not recognize yyyymmdd date   

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.

date: Fri, 22 Aug 2008 08:40:01 -0700   author:   catkin63

RE: Access 2007 External Data Import will not recognize yyyymmdd date   

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.

"catkin63" wrote:

> 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.

date: Fri, 22 Aug 2008 09:07:01 -0700   author:   catkin63

Re: Access 2007 External Data Import will not recognize yyyymmdd date   

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"  wrote in message 
news:33FA2ED6-B8F5-4E9C-9BB5-9078B3CC960D@microsoft.com...
>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.

date: Fri, 22 Aug 2008 09:08:23 -0700   author:   Jeff Boyce

Re: Access 2007 External Data Import will not recognize yyyymmdd d   

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.

"Jeff Boyce" wrote:

> 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"  wrote in message 
> news:33FA2ED6-B8F5-4E9C-9BB5-9078B3CC960D@microsoft.com...
> >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. 
> 
> 
>

date: Fri, 22 Aug 2008 10:26:01 -0700   author:   catkin63

RE: Access 2007 External Data Import will not recognize yyyymmdd d   

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

"catkin63" wrote:

> 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.
> 
> "catkin63" wrote:
> 
> > 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.

date: Wed, 10 Sep 2008 12:38:11 -0700   author:   dejames