locked
Linked Excel Table #Num!-Set to null in query RRS feed

  • Question

  • Good morning all.

    So, I am trying to set up an Access database linked to Excel files and SQL Server tables, where I can run a script to load the excel data into the SQL tables.

    The whole purpose of this is so that people can export Excel files from a program, drop them in a folder, and then click a macro to run the update script.

    Due to the nature of the process, it has to be super easy for the user, or they will whine and not use it, so any resolution involving managing the Excel file is no good. I MUST figure out a way to set these #Num! errors to NULL, or a dummy date, or SOMETHING. I've read a bunch of posts about why it happens, and I get it, but I can't seem to correct for it in any fashion.

    No matter what I do, I cannot get any function to recognize the error and set it to that other something. I've tried several functions where logic might dictate they should work, but they just keep popping out #Num!.It's like in this instance, Access doesn't even acknowledge it as ANYTHING.

    If this is just the way it is, and I need to give up, that is fine, but until I know for sure I will not be able to eat or sleep or focus on anything else (ok, I wills till eat, but not the other stuff). Has anyone ever figured out a way to set these values to something else in a query? If so, please for the love of God share that knowledge. If not, thanks anyway. I'm grateful for any advice or wisdom or fudge if you have some.

    Thursday, May 17, 2018 2:29 PM

Answers

  • In case you want to tinker with the Registry, there are some entries that you can modify that may change the behavior. TypeGuessRows and ImportMixedTypes will generally enable you to either import as the correct data type or import as text. I will modify these when using Jet or ACE OLEDB but I believe they work for Access as well.

    https://msdn.microsoft.com/en-us/library/office/ff844939.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by CluelessCoder Tuesday, May 22, 2018 1:02 PM
    Friday, May 18, 2018 1:04 PM

All replies

  • Have you determined why you are getting the #Num! error? Is it because you have a mix of text and numeric data in a column or is it something on the Excel side?

    I think you need to identify the cause instead of simply discarding what may be valid data in the Excel Worksheet.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 17, 2018 3:10 PM
  • Thanks for replying

    The issue is that there are legitimately unanswered date fields in some instances. The Access Linked Table Wizard sees some dates so it declares the data type as a datetime. The blanks then become #Num! errors.

    If it were for myself to use I would be able to handle it fine by changing the data type in Excel or pulling data into a local table in Access, but it's for another department that is supposed to then be independent using the database. They just export the file to the correct folder and Access is already linked to it so they can get data.

    I was hoping it were possible to handle the error in the SQL on the Access end, something like "IIF(ISERROR([FieldName]) = TRUE, NULL, [FieldName])", or something like that. I tried using IsDate, converting to a number and then saying "if > 0 then", etc, etc. No matter which way I try, the #Num! doesn't flinch.

    Like I said before, if there's no solution then so be it, just want to make sure I am not missing something.

    Thursday, May 17, 2018 8:03 PM
  • Have you tried using the Nz function?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 17, 2018 9:25 PM
  • Yeah, I tried that, I tried treating the error as actual text, multiplying by a number, converting the dates to integers and then saying where value > 0, I ever tried creating an actual error on all the values (divided by zero, multiplied by a letter), and got the corresponding error on the true values, but #Num! remained. I think it just isn't something Access won't handle.

    It's ok, not everything is possible. I can at least say that I tried. Thanks for following up though.

    Friday, May 18, 2018 12:10 PM
  • In case you want to tinker with the Registry, there are some entries that you can modify that may change the behavior. TypeGuessRows and ImportMixedTypes will generally enable you to either import as the correct data type or import as text. I will modify these when using Jet or ACE OLEDB but I believe they work for Access as well.

    https://msdn.microsoft.com/en-us/library/office/ff844939.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by CluelessCoder Tuesday, May 22, 2018 1:02 PM
    Friday, May 18, 2018 1:04 PM
  • In case you want to tinker with the Registry, there are some entries that you can modify that may change the behavior. TypeGuessRows and ImportMixedTypes will generally enable you to either import as the correct data type or import as text. I will modify these when using Jet or ACE OLEDB but I believe they work for Access as well.

    https://msdn.microsoft.com/en-us/library/office/ff844939.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Cool, I will take  a look at this. Thanks for your persistence in assisting me :)
    • Marked as answer by CluelessCoder Tuesday, May 22, 2018 1:02 PM
    • Unmarked as answer by CluelessCoder Tuesday, May 22, 2018 1:02 PM
    Monday, May 21, 2018 12:45 PM