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.