Date/Time Issue

[bdhFS]

I am having a issue with some data I imported from a CSV file. First of all the Date/Time field came over as 'Text' data type instead of a 'Date/Time' data type. I tried to change the settings to a Date/Time format while importing but then it would give me an error and the date/time would not load at all. I need Access to see this as Date/Time for calculations and such, but I am having a hard time converting it. I have tried in a query using Format and CDate with no luck. Here is what the date/time looks like in the DATA_DATE field.

09-OCT-09 12.00.00.000000000 AM

This time format is new to me and I would rather it be 09/OCT/2009 12:00:00 AM as well as be a date/time datatype. Any help would be appreciated. I was thinking I will probably have to parse this "String" of text out using function like Len, Right, Left etc.

Thanks for help


Using Access 2010

[ssanfu]

Try setting up an import specification. I tells the wizard what the data types should be.

[bdhFS]

I tried to change the settings to a Date/Time format while importing but then it would give me an error and the date/time would not load at all.