Import Spec and date format

  • Thread starter Thread starter Steve_A
  • Start date Start date
S

Steve_A

I have been using access 97 since about 2000 and have updated some stuff over
time to 2K and XP but now I am going to 2007 and my import specifications are
having problems.

Most of my data comes from a mainframe and I bring it down as fixed width
and most is good as I use most as “TEXT†but some have different date
formats. This one is a date format of “YYMMDD†(no / or -) my stuff for
97/02 and been bringing it in just fine as mm/dd/yyyy but 2007 errors out
saying “Type Conversion Error†and leaving the field blank.

I even tried making a new Import Specification and it still does not like
it? Did something change that much in 2007?

tia
 
I am having the same issue. Date imports from a text file that worked in 2003
do not work in 2007. I was importing from the format yymmdd
 
I have the same problem. Date imports from text data files that worked in
access 2003 do not work in 2007.

I found this solution on another website

The only solution that I can find is to convert the text data to a date. The
only workaround I can see is to import that data into a table with the date
as a text field. You then simply run a update query to send this data to
another column in the table (or simply build a expressin that makes the date
out of this..and append to the production table).

Assuming your “text†data as the text date field is called txtDateF

Then, for your expression in the query builder, you can use:

NewDateField:
DateSerial(Left([txtDatef],4),Mid([txtDatef],5,2),Right([txtDatef],2))
 
Bill I gave that a try but there are still some problems, it dropped leading
0 in date and it did not change yymmdd to mmddyy

Did you get it to work?

Bill B said:
I have the same problem. Date imports from text data files that worked in
access 2003 do not work in 2007.

I found this solution on another website

The only solution that I can find is to convert the text data to a date. The
only workaround I can see is to import that data into a table with the date
as a text field. You then simply run a update query to send this data to
another column in the table (or simply build a expressin that makes the date
out of this..and append to the production table).

Assuming your “text†data as the text date field is called txtDateF

Then, for your expression in the query builder, you can use:

NewDateField:
DateSerial(Left([txtDatef],4),Mid([txtDatef],5,2),Right([txtDatef],2))




Steve_A said:
I have been using access 97 since about 2000 and have updated some stuff over
time to 2K and XP but now I am going to 2007 and my import specifications are
having problems.

Most of my data comes from a mainframe and I bring it down as fixed width
and most is good as I use most as “TEXT†but some have different date
formats. This one is a date format of “YYMMDD†(no / or -) my stuff for
97/02 and been bringing it in just fine as mm/dd/yyyy but 2007 errors out
saying “Type Conversion Error†and leaving the field blank.

I even tried making a new Import Specification and it still does not like
it? Did something change that much in 2007?

tia
Click to expand...
Click to expand...
 
Back
Top