Solved

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

Posted on 2006-07-06
5
296 Views
Last Modified: 2012-06-21
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?      
0
Comment
Question by:zimmer9
5 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 17055125
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 17055235
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17055897
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
 

Author Comment

by:zimmer9
ID: 17063043
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
 
LVL 26

Accepted Solution

by:
jerryb30 earned 500 total points
ID: 17063247
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now