Import excel dates into access

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
LVL 3
Jon JaquesInformation TechnologistAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Jon JaquesInformation TechnologistAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.