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.
Liberty4allRetiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I would start by reformatting that entire column in Excel to be Number.
And ... 'blanks' can be either actual Null or Zero Length Strings ("").
I see this a lot ... and usually you just have to mess with the Excel side until it clears up.
Liberty4allRetiredAuthor Commented:
I reformatted the entire column as Number in Excel.  Closed and reopened Access but the result did not change.  Access still reads it as Number (field size is Double).  I then highlighted the column in Excel and pressed F2 but still no change in result.  I also tried using "" (double quotes with no space) instead of IS NULL in my query and got a message saying "Data type mismatch in criteria expression".  I'm guessing that confirms no zero length string values exist.

What should I try next?
Fabrice LambertConsultingCommented:
If you open the linked table in creation mode, does access show the column as numeric ?

Be aware that when linking to Excel, the system scan the top rows (default is 8) to "guess" the datatype. It choose wichever according to the majority.
In that case, you have 2 solutions available:
- Make sur the top rows are filled up with data of the desired data type, this can be done by manipulating your datasheet.
- Enforce everything as text. This is done by adding a single quote in front of each cell value, this can be tricky with formulas (make a backup first).
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... the bottom line is ... one or more values are hosed up in that data ... such that Access cannot resolve the issue.
As Fabrice noted ... you might try importing that Column as Text ... which you can set up in the Import Wizard initially ... each column can be set to whatever data type.

Also besides testing for Is Null ... also test for Len([YourFieldName] = 0  (or >0) ... instead of using just ""
Liberty4allRetiredAuthor Commented:
The first 3,334 records have valid numeric values in the Excel file.  When I test for length of the field for those areas which are "null" I get #Num! as the length.  Is it safe to assume that #Num! means the same as null?  If so, I can note that in my documentation and move on.  I just don't want to intentionally overlook a problem.
Fabrice LambertConsultingCommented:
AFAIK, you get the #Num! vale mean the data isn't numeric. So there may be something wrong with your data source.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Liberty4allRetiredAuthor Commented:
Fabrice: I copied the column that was formatted as numeric to another and formatted it as text.  I then ran a query searching for nulls and it only returned three.  I'm guessing those are the same records I pressed F2 on.  I then ran the query to search for "" values and it returned over 1500.  This confirms those "empty" cells have a zero length value in them.  Can you please tell me what formula to use in Excel to convert zero length string values to null?
Fabrice LambertConsultingCommented:
I think if datas are enforced to string, they can't be null.
On he other hand, you'll have less troubles with non numeric datas mixed with numeric ones.
Liberty4allRetiredAuthor Commented:
Converting the column to text format in Excel was the key to solving this.  The problem was exacerbated due to something in our Citrix environment that was making the file not work as expected.  After replacing the file I found that using "" or IS NULL (together) in the filter produced results as expected (no #Num! values appear).  I appreciate both of your contributions as I would not have thought to solve this by converting the column to text format.  I wasn't aware that zero length string values could exist with numeric data.

I appreciate your time
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.