<div>
I would start by reformatting that entire column in Excel to be Number.<br />
And ... 'blanks' can be either actual Null or Zero Length Strings (&quot;&quot;).<br />
I see this a lot ... and usually you just have to mess with the Excel side until it clears up.
</div>


<div>
I reformatted the entire column as Number in Excel. &nbsp;Closed and reopened Access but the result did not change. &nbsp;Access still reads it as Number (field size is Double). &nbsp;I then highlighted the column in Excel and pressed F2 but still no change in result. &nbsp;I also tried using &quot;&quot;&nbsp;(double quotes with no space) instead of IS NULL in my query and got a message saying &quot;Data type mismatch in criteria expression&quot;. &nbsp;I'm guessing that confirms no zero length string values exist.<br />
<br />
What should I try next?
</div>


<div>
The first 3,334 records have valid numeric values in the Excel file. &nbsp;When I test for length of the field for those areas which are &quot;null&quot; I get #Num! as the length. &nbsp;Is it safe to assume that #Num! means the same as null? &nbsp;If so, I can note that in my documentation and move on. &nbsp;I just don't want to intentionally overlook a problem.
</div>


<div>
Fabrice: I copied the column that was formatted as numeric to another and formatted it as text. &nbsp;I then ran a query searching for nulls and it only returned three. &nbsp;I'm guessing those are the same records I pressed F2 on. &nbsp;I then ran the query to search for &quot;&quot;&nbsp;values and it returned over 1500. &nbsp;This confirms those &quot;empty&quot; cells have a zero length value in them. &nbsp;Can you please tell me what formula to use in Excel to convert zero length string values to null?
</div>


<div>
Converting the column to text format in Excel was the key to solving this. &nbsp;The problem was exacerbated due to something in our Citrix environment that was making the file not work as expected. &nbsp;After replacing the file I found that using &quot;&quot;&nbsp;or IS NULL (together) in the filter produced results as expected (no #Num! values appear). &nbsp;I appreciate both of your contributions as I would not have thought to solve this by converting the column to text format. &nbsp;I wasn't aware that zero length string values could exist with numeric data.<br />
<br />
I appreciate your time
</div>


<div>
<div class="content wysiwyg-content">
I have an Access 2010 file linked to an Excel document. &nbsp;In my query I'm filtering for IS NULL on a field in which the data type is Number (field size is double). &nbsp;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. &nbsp;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.<br />
<br />
I've not encountered this before so am not sure what to do. &nbsp;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. &nbsp;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?<br />
<br />
Any suggestions would be appreciated.
</div>
</div>


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.

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

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.