Jon Jaques
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Aha! You nailed it... it's just the " EST" that was keeping Access from recognizing the data as a date.
Thanks for your help!!!
Thanks for your help!!!
assuming the field name is dteField
select dteField, cdate(left([dteField], instr([dteField], " ")-1)) as YourDate
from tableName