after importing dates=text and not date format??

  • Thread starter Thread starter Shivalee Gupta via AccessMonster.com
  • Start date Start date
S

Shivalee Gupta via AccessMonster.com

i am importing files from excel into access as tables via this command:
DoCmd.TransferSpreadsheet acImport, 8, "agr_1016", "D:\database\user02.xls", True
the import works fine. but i have 5 columns in this file which contain dates in this format: 29.09.2004 . i converted these columns using format cells into date and 3/14/1998 format. but after coming to access, the table has the column field not as date/time, but as text. hence, all my queries based on the table having dates are giving hazardous results.
i am in deep trouble. any help would be highly appreciated.
regards,
shivalee
 
Shivalee

Another approach to importing data is to use "temporary" tables to receive
the import, then build append/update queries to convert the data while
populating more permanent tables. It is your permanent tables that you'd
refer to with your queries.

It is not all that uncommon that data imported from Excel requires some
additional "parsing"/conversion before it fits into a well-normalized Access
relational database...

--
Good luck

Jeff Boyce
<Access MVP>

Shivalee Gupta via AccessMonster.com said:
i am importing files from excel into access as tables via this command:
DoCmd.TransferSpreadsheet acImport, 8, "agr_1016", "D:\database\user02.xls", True
the import works fine. but i have 5 columns in this file which contain
Click to expand...
dates in this format: 29.09.2004 . i converted these columns using format
cells into date and 3/14/1998 format. but after coming to access, the table
has the column field not as date/time, but as text. hence, all my queries
based on the table having dates are giving hazardous results.
 
can anyone else help me on this...anybody?
regards,
shivalee
 
Back
Top