Solved

Import excel dates into access

Posted on 2015-02-16
3
146 Views
Last Modified: 2015-02-16
Hello,

I have a process in MS Access that imports a downloaded CSV file into MS Access using docmd.TransferText, but the date fields are not recognized by Access. I've tried a couple of things to convert these dates, but nothing has worked so far. The imported date values look like this:

12-Feb-2015 08:12:28 PM EST
14-Feb-2015 02:57:23 PM EST

I'm importing the list into a working table, and the field to receive that date value is of type Text; I tried setting it to date/time, but then access just refused to import that fields' data. I then added an extra field at the end of the working table and have tried various formats and functions to manually push the date into that field, but I always get an error about mismatched field types.

How can I format this date value so that I can sort these records chronologically?

Any help would be much appreciated!!!!

--J
0
Comment
Question by:CoastalData
  • 2
3 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
create a query with this column
assuming the field name is dteField

select dteField, cdate(left([dteField], instr([dteField], " ")-1)) as YourDate
from tableName
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
another way is to remove the "EST" from the text date
you can do this with an update query

update tableName set [dteField]=Replace(dteField," EST","")
0
 
LVL 3

Author Closing Comment

by:CoastalData
Comment Utility
Aha! You nailed it... it's just the " EST" that was keeping Access from recognizing the data as a date.

Thanks for your help!!!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

6 Experts available now in Live!

Get 1:1 Help Now