Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010

    Unanswered: #Num! error in excel and access linked tables

    Good afternoon,

    I am trying to link an excel spreadsheet with an access table. I have used the "transferspreadsheet" macro and it all seems to work ok except for a few errors. There are several fields that, instead of displaying the data, display the error #Num!. I have checked the data types in the excel spreadsheet and changed them to appropriate fields but still get these errors.

    What confuses me is that it only gives this error for certain data entries within the same field. One of the fields is Work Code and the error only discriminates against certain entries.

    Can somebody give me a good fix for this? Any suggestions would be much appreciated.

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    Are you sure you've formatted the entire excel columns correctly before importing?

    When importing an excel file, MSAccess likes to interpret what the field type is by what the values are in the first row of the excel sheet. For example, if the 1st row of Column B in the excel sheet is a number and the 2nd row of column B in the excel sheet is a text value, MSAccess might interpret that field (when importing as an integer type) which would give you errors on the text values.

    Also check for bad date type values. A date value of 3/69 versus 3/1/2010 would cause problems when importing and MSAccess interprets this as a date type field.

    Since your problem is limited to certain values (not entire columns or fields), I'm guessing it has something to do with the formatting in excel of those fields.

    You can also try linking to the excel file to see if this changes anything with the #Num error. If all data looks ok in the linked excel table, then use a query to append the data to an MSAccess table (or try a make-table query).
    Last edited by pkstormy; 04-10-10 at 00:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts