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
Solved

Import excel dates into access

Posted on 2015-02-16
3
154 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40612308
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40612328
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
ID: 40612658
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

839 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