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()
Replaces NULL with the specified replacement value.
ISNULL ( check_expression , replacement_value )
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.
Nz(variant[, valueifnull ])
VBA's IsNull() is Equivalent to Jet's Is Null
Returns a Boolean value that indicates whether an expression contains no valid data (Null).
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.
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.
Nz(Amt, 0) use
IIf(Amt IS NULL, 0, Amt), especially within queries.
There are several good reasons for doing this:
IIfis a ternary
statementoperator so it uses efficient short-circuiting boolean evaluation
IIfis built-in to the Jet Expression Service, so you avoid calling into VBA
IIfpreserves 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 Nullcombo 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.
Nz()normally results in more readable code
- The Jet/ACE expression service's
IS NULLstatement is not available in VBA
IsNull()VBA function provides no performance boost versus
Nz()the way the
IS NULLJet 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).