Alternative to Nz and IIF(Is Null)

Question

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.

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.

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 NZ(subquery, 0), but ADO.Net throws the following exception:

Undefined function 'NZ' in expression

I learned that it's not possible to use a VBA function in ADO.Net since it's part of the Access.Application object.

I also considered using IIF(subquery Is Null, 0, subquery), but that would force me to duplicate the subquery, and force Access to execute the subquery twice (probably). 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.

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?

Answer 1

Sorry, there's no other way. The cleanest method is going to be

Iif(IsNull([Data]), 0, [Data])

NZ is a function specific to the Access code library, so you won't be able to use it.

Answer 2

Try this:

int? x = null;

// Set y to the value of x if x is NOT null; otherwise,
// if x == null, set y to -1.
int y = x ?? -1;

To learn more, see microsoft help