Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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.
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.
Asked:
-
4
3
2
4 Solutions
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?
What should I try next?
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).
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).
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 ""
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 ""
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.
AFAIK, you get the #Num! vale mean the data isn't numeric. So there may be something wrong with your data source.
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?
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.
On he other hand, you'll have less troubles with non numeric datas mixed with numeric ones.
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
I appreciate your time
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
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.