How to convert a txt file field that is a date into an Access table as a date type field ?

I created a DTS package to utlimately create an Excel report.
I am starting out with a txt file, that contains a date field.
One of the first steps I perform with the DTS package is a "Transform Data Task" with a SOURCE of the txt file and the DESTINATION is an Access table.
For a date field in the txt file called [Invoice Date], I defined the Access table with a receiving field also called [Invoice Date] with a field type of Datetime.

I was trying to transfer the text field called [Invoice Date] on the txt file with date values to an Access field also called [Invoice Date] as a date field because I need to perform some date comparisons with this field once the txt file is loaded into the Access table.
 
This causes the following error when I run the DTS package:

Transform Data Task: undefined
The number of failing rows exceeds the maximum specified.
Transform Copy 'DTS Transformation_6' conversion error:
Conversion invalid for data types on column pair 1
(source column 'col006' (DBTYPE_STR), destination column 'Invoice Date' (DBTYPE_DBTIMESTAMP))

Is there a way to convert the txt date field to a date type field in the Access table ?

If I don't convert the data type to a date field when I move the txt file to an Access table, then if I move the date field to Excel, the field will be a text field
and won't I have a problem conveting the date to a different date format in Excel. Also won't I have a problem testing date ranges with a date field that is
of type nvarchar in the Access table?      
zimmer9Asked:
Who is Participating?
 
jerryb30Connect With a Mentor Commented:
Do not believe you can alter more than one column at a time in Access.  You would have to execute to separate DDL statements in queries/code.

Alter tablename alter column [col x] date
and then
alter tablename alter column [col y] date
0
 
Raynard7Commented:
What I would do is make the column text in access;

Then either create an additional column which is a date - and once the data is imported run a sql statement to update the column and set it to cdate([Invoice Date])

Or you could use a query to covert the column and use this query wherever you refer to the table - and it is just like it was a date field.
0
 
jerryb30Commented:
IN SQL:
ALTER TABLE TABLENAME ALTER COLUMN COLUMNAME DATE
But why would a field that is always a date be a text field in the first places?
0
 
GRayLCommented:
What is the format of the text date field. Remember Access requires date separators in a text field to convert it to a date.  If the text date is 20060706 - it won't work.  You have to import the text field as text, and build a date using something like:

UPDATE myTable SET myDateFld=DateSerial(Left(txtDate,4),Mid(txtDate,5,2),Right(txtDate,2));
0
 
zimmer9Author Commented:
How would I extend the following ALTER TABLE statement to include 2 fields in terms of the syntax ? For ex: using Col X and Col Y

ALTER TABLE TABLENAME ALTER COLUMN COLUMNAME DATE
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.