Import Spec for Date with No Delimiter

[Keith]

I have a simple import spec that works fine in Access 2003 that imports a
date that has no delimiter (i.e. 20090515). I check the "Four Digit Years"
box, select YMD Date Order and blank out the Date Delimiter Field. All
worked fine until Access 2007. Can anyone help a simple Access user? I am
also now running Windows Vista instead of XP

 

[Albert D. Kallal]

I have a simple import spec that works fine in Access 2003 that imports a
date that has no delimiter (i.e. 20090515). I check the "Four Digit
Years"
box, select YMD Date Order and blank out the Date Delimiter Field. All
worked fine until Access 2007. Can anyone help a simple Access user? I
am
also now running Windows Vista instead of XP

This is a known issue with access 2007.

You have
to import the data into a text column, and then run an update query like:

update MyTablename set RealDateCollum =
dateserial(left(textDateCollum,4),mid(textDateCollum,5,2),mid(textDateCollum,7,2))

So, import the column as a text column, and then process as above. A bit
messy, but that is about the only workaround I can think of right now...


I have already reported this bug many months ago. There is a new sp update
coming out for office + access very soon.
The office blog stats that the new update is slated for the end of this
month. That is not even 10 days away.

Lets hope the sp update will fix this problem for everyone....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)




The only workaround rgith now
This issue also been f

 

[Keith]

Thanx Albert. That worked. I sure hope the fix in 2 days correct this. If
it doesn't, is there a way to do it without adding a column to the table? I
have about 140 tables that I import from txt files with dates and each table
has several date fields formatted tis way.

 

[Albert D. Kallal]

Thanx Albert. That worked. I sure hope the fix in 2 days correct this.
If
it doesn't, is there a way to do it without adding a column to the table?
I
have about 140 tables that I import from txt files with dates and each
table
has several date fields formatted tis way.

I don't see easy workaround. You could certainly write code to automatic
pull in all of the 140 tables and THEN update the column automatic for you
(but, that code will still have to do the steps I outline in my previous
post).

So, we are only a few days away from the upgrade. I would start just writing
the code to do the imports automatic and "assume" that the date problem will
be fixed. So, by the time you have this import working automatic as a one
button click kind of thing, you be read to test new sp update coming...

since this issue came up so long ago, then bets are good that the next
update + bug fix for office will fix this....