<div>
Use IIF() and IsNull(), something along the lines of<br />
<br />

<pre><code id="code-20-42770271-1">IIF(IsNull([Field1]), Null, Val([Field1]))</code></pre>
You can replace Null with whatever is appropriate for your situation.
</div>


Use IIF() and IsNull(), something along the lines of



IIF(IsNull([Field1]), Null, Val([Field1]))

You can replace Null with whatever is appropriate for your situation.

<div>
Hi Thanks for that, unfortunately it keeps putting in a zero instead of leaving the result blank
</div>


Hi Thanks for that, unfortunately it keeps putting in a zero instead of leaving the result blank

Tom Crowfoot

<div>
Brilliant, thank you that works well, &nbsp;but just as a side question &amp;&nbsp;I'll open up a new request - any idea why if I link an excel spreadsheet to the database any query using val it doesn't appear in the list of queries
</div>


Brilliant, thank you that works well,  but just as a side question & I'll open up a new request - any idea why if I link an excel spreadsheet to the database any query using val it doesn't appear in the list of queries

<div>
You can link a worksheet or a range from Excel, not a query.
</div>


You can link a worksheet or a range from Excel, not a query.

<div>
Ok cool thanks for that, really appreciated
</div>


Ok cool thanks for that, really appreciated

<div>
<div class="content wysiwyg-content">
Hi<br />
<br />
In an Access Query I need to convert text to a number - I've been using var([Field1]), but if the field was blank then it returns a zero, is there a way to convert the text to a number &amp;&nbsp;if the field was blank then it remains blank?<br />
<br />
Can anyone help?
</div>
</div>


Hi

In an Access Query I need to convert text to a number - I've been using var([Field1]), but if the field was blank then it returns a zero, is there a way to convert the text to a number & if the field was blank then it remains blank?

Can anyone help?

Convert Text to Number handling blanks

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.