Avatar of Liberty4all
Flag for United States of America

asked on 

What causes #Num! to appear in an Access query where filter for a numeric field = IS NULL in a linked Excel file?

I have an Access 2010 file linked to an Excel document.  In my query I'm filtering for IS NULL on a field in which the data type is Number (field size is double).  This returns a value of #Num! for 1,530 records but since the file has over 30,000 records I don't know if that number is correct.  When I look at a few of these in Excel the cells are blank for that column and when I press F2 to determine if some other value appears the #Num! in my query disappears.

I've not encountered this before so am not sure what to do.  If having #Num! appear when searching for IS NULL on a numeric field in a linked Excel file is the same as a null value then fine, but I would prefer to have the query display blanks to eliminate ambiguity.  Is it possible to reformat the column in Excel to cause this to happen, or can I do something in Access to achieve the same thing?

Any suggestions would be appreciated.
Microsoft AccessMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment

8/22/2022 - Mon