Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

670 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