troubleshooting Question

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

Avatar of Liberty4all
Liberty4allFlag for United States of America asked on
Microsoft AccessMicrosoft ExcelMicrosoft Office
9 Comments4 Solutions154 ViewsLast Modified:
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.
Join our community to see this answer!
Unlock 4 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros