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

Liberty4all
Liberty4all used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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.
Liberty4allRetired

Author

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 LambertConsulting
Distinguished Expert 2017
Commented:
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).
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
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 ""
Liberty4allRetired

Author

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.
Consulting
Distinguished Expert 2017
Commented:
AFAIK, you get the #Num! vale mean the data isn't numeric. So there may be something wrong with your data source.
Liberty4allRetired

Author

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 LambertConsulting
Distinguished Expert 2017
Commented:
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.
Liberty4allRetired

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial