MS Access how to import string into Date/Time

Dear all,

I have an excel file that I'm importing into access.  One of the columns (fields to import) contains date information i.e. 01/05/2012 00:00:00 Tue.  When I import it into Access as a Date with Time I end up with import errors and it doesn't work.  I tried to change the format of the excel cells to Date but it makes no difference.

My next step is to link records by date, this table and another table where the Date is separate fields Month, Day, Timestep. The timestep is an integer from 1 to 48 for each day (half hourly data for one day).

How do I import this data into the database?  I don't need the actual day information (i.e. Tue).  Is it possible to parse the date using VB script or a macro into Year, Month, Day, Hour, Minute, Second etc.? Or include the timestep for the time?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
First import or link the Excel file where you specify the field as text.
Then pull the data via a query using the linked or imported table as source.
In this query, convert the string to a date using this expression:

    TrueDate: CDate(Mid([YourTextDateField],1,19))

If some records contains Null for this field, use CVDate:

    TrueDate: CVDate(Mid([YourTextDateField],1,19))

Also convert other fields as needed.
Now use this query as source when you proceed.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If the import is temporary, then stick with the query solution.  However, if the import is permanent, I would store the reformatted data in a proper date/time column.  Once the update is successful, then delete the original text column.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.