T-SQL ISNULL() vs. VBA IsNull() vs. Jet IS NULL vs. Access VBA Nz()

There are many ways to check for NULL as an MS Access developer.

Which one you choose often depends on where you are using it:

• An Access query?
• A SQL Server query?
• A VBA function?

Many of these things have very similar names, yet work quite differently.  Others have very different names, but work quite similarly.  

Let's explore the myriad ways to handle NULLs as an MS Access developer.

T-SQL's ISNULL() is Equivalent to Access VBA's Nz()

T-SQL ISNULL() function:

Replaces NULL with the specified replacement value.

Syntax: ISNULL ( check_expression , replacement_value )

The Nz() function:

You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Syntax: Nz(variant[, valueifnull ])

VBA's IsNull() is Equivalent to Jet's Is Null

VBA IsNull() function:

Returns a Boolean value that indicates whether an expression contains no valid data (Null).

Syntax: IsNull(expression)

Jet IS [NOT] NULL statement:

Evaluates whether the expression passed to it is null (or, optionally, not null).  Returns a -1 or 0 to indicate whether the expression evaluated to True or False, respectively.

Syntax: expression IS [NOT] NULL

The Most Efficient Way to Deal with Nulls in Jet/ACE Queries

While Nz() may be the most convenient option, it is not the most efficient option.

Instead of Nz(Amt, 0) use IIf(Amt IS NULL, 0, Amt), especially within queries.

There are several good reasons for doing this:

• IIf is a ternary statement operator so it uses efficient short-circuiting boolean evaluation
• IIf is built-in to the Jet Expression Service, so you avoid calling into VBA
• IIf preserves the data type of your field
• Nz() returns a Variant, so a Currency field gets treated like a String
• When used in a WHERE condition, Nz() requires a full table scan for linked tables, while the equivalent IIf/Is Null combo gets passed through and applied at the source via ODBC
  UPDATE [2022-05-13]: This statement is not true; see here for details.

Use Nz() within Access VBA

While Nz() is a poor choice to use in queries, it is generally your best option within Access VBA.

• Using Nz() normally results in more readable code
• The Jet/ACE expression service's IS NULL statement is not available in VBA
• The IsNull() VBA function provides no performance boost versus Nz() the way the IS NULL Jet statement does

Note that the Nz() function is NOT part of the standard VBA library.  Rather, it is a function of the Access.Application object.  The Access.Application object is available in the global namespace when developing within Microsoft Access, which is why you can call Nz() directly (rather than as a member of the object to which it belongs).

Image by Antonio Sales Sales from Pixabay