locked
#Num! Error in linked Excel tables - Access 2010 RRS feed

  • Question

  • When I import and link an Excel file in Access, I am not able to change the Field Type and it's reading a Text field in Excel as a Number, which is not a problem but when the field is empty it throws a "#Num!" error and I cannot do anything with that field.

    I know there are alternate solutions to import the data in a temp table, etc but the purpose of linking a table is so we don't have to do the extra steps.

    It's very frustrating that Access cannot interpret Null or Empty values when the data type is set to Number.

    I tried catching the error using IsError, IsNull, IsNumeric but all fail, it doesn't matter what I use it always gives me #Num! error.

    I even tried changing the column type to "Text" in Excel but it doesn't work either, any solutions to catch the error so I can make it into something?

    For example iif(IsNull([field]),0,[field])

    Thank you 

    Friday, October 10, 2014 5:04 PM

Answers

  • It is a strange quirk indeed, but that's how the engine works.

    So if a temp table isn't a workable solution, you might make sure you place the values you want in the first 6-8 rows of your spreadsheet, so Access can determine what value it needs to show.

    If that doesn't work, you need to fix the data upfront in Excel itself, to show correct results in Access.

     

    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Friday, October 10, 2014 6:08 PM
  • Importing Excel data into Access has always had the problem or identifying the correct data types.

    What I do is first import the Excel data into a temporary table consisting of all text fields, naming the fields F1, F2, F3, etc., plus an autonumber ID. Specify HasFieldNames = False.
    The Excel column heads are then in the first row of the table.
    You can then test for the correct column heads & delete that row. Then scrub the data if necessary and append it to the desired table.

    Friday, October 10, 2014 6:20 PM
  • Try the Calculated Column I described in the MSDN Thread

    Getting Access to read Excel numbers

     


    Van Dinh

    Friday, October 10, 2014 9:12 PM

All replies

  • Here is an ADODB routine for reading data from an Excel .xlsm file (works the same for .xlsx).  With this routine you need 1) to add reference to Microsoft ActiveX Data Objects x.x Library -- in Tools/References  2) you need to know in advance how many Excel columns (and data types of each column) you will be importing  3) create a receiving table in your Access DB with the same number of columns (except for your rowID autonum column in the Access table) as in the Excel data file -- with the same data types (if you have decimal values -- make them a double data type in Access -- it's just easier).  Here is the code sample (this is way more reliable than linking Excel sheets):

    Sub ReadDataFromXlsx()
       Dim cmd As New ADODB.Command, RS As ADODB.Recordset
       Dim rsDAO As DAO.Recordset, i As Integer
          
       cmd.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Code_T9\Excelstuff\VendorReportTest.xlsm;Extended Properties=""Excel 12.0 Xml;HDR=YES"""
       cmd.CommandText = "Select * from [sheet1$]"  '-- or the sheet name if different than "Sheet1"
       Set RS = cmd.Execute
       Set rsDAO = CurrentDb.OpenRecordset("tblVendorRptTest") '--table in Access to receive data from Excel
       Do While Not RS.EOF
          rsDAO.AddNew
          '--transfer the data from the ADODB recordset RS to the DAO recordset rsDAO in this For Loop
          For i = 0 To RS.Fields.Count - 1
             rsDAO(i + 1) = RS(i)
          Next
          rsDAO.Update
          RS.MoveNext
       Loop
       Debug.Print "Done!"
    
    End Sub


    Rich P


    • Edited by Rich P123 Friday, October 10, 2014 5:55 PM .......
    Friday, October 10, 2014 5:43 PM
  • It is a strange quirk indeed, but that's how the engine works.

    So if a temp table isn't a workable solution, you might make sure you place the values you want in the first 6-8 rows of your spreadsheet, so Access can determine what value it needs to show.

    If that doesn't work, you need to fix the data upfront in Excel itself, to show correct results in Access.

     

    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Friday, October 10, 2014 6:08 PM
  • Importing Excel data into Access has always had the problem or identifying the correct data types.

    What I do is first import the Excel data into a temporary table consisting of all text fields, naming the fields F1, F2, F3, etc., plus an autonumber ID. Specify HasFieldNames = False.
    The Excel column heads are then in the first row of the table.
    You can then test for the correct column heads & delete that row. Then scrub the data if necessary and append it to the desired table.

    Friday, October 10, 2014 6:20 PM
  • Try the Calculated Column I described in the MSDN Thread

    Getting Access to read Excel numbers

     


    Van Dinh

    Friday, October 10, 2014 9:12 PM