title: Alternative to Nz and IIF(Is Null)
body: <p>I have a query in Access in which one of the fields contains a subquery returning the sum of a specific field from a related table. The result of the subquery is used in further calculations in the main query.</p>
  
  <p>This is used in a homemade reporting application in VB.Net (C# would be fine) using ADO.Net. Queries are stored in a table in Access to allow us to correct small bugs or adding temporary features related to the data without publishing our application again.</p>
  
  <p>The problem is that it's possible that the subquery returns no result (null). Furthermore, a null value used in calculations will also return a null value. Therefore, I need to replace the null value by 0 if that's needed. I added <code>NZ(subquery, 0)</code>, but ADO.Net throws the following exception: </p>
  
  <pre><code>Undefined function 'NZ' in expression
  </code></pre>
  
  <p>I learned that it's not possible to use a VBA function in ADO.Net since it's part of the Access.Application object.</p>
  
  <p>I also considered using <code>IIF(subquery Is Null, 0, subquery)</code>, but that would force me to duplicate the subquery, and force Access to execute the subquery twice <em>(probably)</em>. Since the subquery is rather complex (and so is the main query), I want to avoid that, at least for the sake of readability, if not performance.</p>
  
  <p>Is there another way to replace a null value by another value, or to make sure my subquery will always return a non-null sum even with no matching record?</p>