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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.