Excel date importing as #Num

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, this is really driving me mad. When I import data from a sheet in Excel,
the date fields are coming in with #NUM rather than the date. It is a date
in Excel and I have tried formatting it as a date as well. I would be really
grateful for any ideas!
Thanks
 
You are probably importing into a new table. Acess tries to determine the
data type of the imcoming data by looking at the first few rows. This is a
common problem when importing Excel data. Access defaults text field lengths
and numeric data types as defined in Tools--> Options, Tables/Queries tab.
So a lot of wasted space is created as well as getting things wrong.

In most cases, it is better to import to an existing table you have created
with the desired data types and field lengths. My personal preference is to
link to the spreadsheet, delete the data in the destination Access table, and
use an append query to move the data from Excel into Access. So:

Create the Link
Delete the old data (Currentdb.Execute("DELETE * FROM SomeTable;")
Run the Append query
Drop the Link
 
Back
Top