Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
315 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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

926 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