I am trying to import to MS Access XP from MS Excel XP. The particuar field I am having trouble with is a date/time field. The format that the date/time is in is:
"2005-01-14 11:15:44 PST"
The time zone is always set as "PST"
The problem I am having is that when it is imported into MS Access, it is imported as Text and I cannot figure out how to change the format so the data is not deleted upon import. How can I set the format up as date/time? Any help on this matter would be greatly appreciated.
I'll give you a little more info so you can understand my situation. I sell product over the internet. The company I sell through lets me download reports (15, 30, 60 days worth of sales) the report is in a spreadsheet format. I sell roughly 1500-2000 items per month and am trying to figure out a timely way of importing the info into Access (hopefully not touching the spreadsheet.)
The format the date/time when I recieve it is:
2005-01-15 12:15:45 PST and when imported into Access, it is read as TEXT.
Is there any way to write a format line in design view to make this a DATE/TIME field instead of just a TEXT field (so I can do queries for particular months)?
Import with your wizard as normal. Copy the structure of the table you make (SourceTable) to (TargetTable) and in design change the DateTxt field to Date type. Now you have an empty table but with a proper date field. Make a query as follows: INSERT INTO TargetTable ( NewDate )
SELECT (CDate(Left([DateTxt],19))) AS expr1
I've just tried it and it works quite well. Whether you delte and append or just append is up to you.