Handle Null Values with Nz and IIf in Access

Null means that a value is missing or unknown. It is different from zero and different from a zero-length string. Microsoft Access provides Nz, IsNull, and IIf for handling these cases, but each function serves a different purpose.

Last updated: August 28, 2026.

Replace Null with a usable value

Use Nz when a calculation or display expression needs a fallback value:

SELECT ProductName,
       UnitPrice,
       Nz([Discount], 0) AS DiscountValue,
       [UnitPrice] - Nz([Discount], 0) AS FinalPrice
FROM Products;

Always supply the second argument in a query. It makes the intended type clear:

• Nz([Discount], 0) for a number.
• Nz([Notes], "") for text.
• Nz([Quantity], 0) before arithmetic.

Test whether a value is Null

Use Is Null in query criteria:

SELECT CustomerID, CompanyName, EmailAddress
FROM Customers
WHERE EmailAddress Is Null;

Use IsNull inside a calculated expression:

SELECT OrderID,
       IIf(IsNull([ShippedDate]), "Pending", "Shipped") AS ShippingStatus
FROM Orders;

Do not write [EmailAddress] = Null. Any ordinary comparison with Null produces an unknown result rather than True.

Choose between Nz and IIf

Be careful: IIf evaluates both result expressions

IIf evaluates both its true and false expressions. It therefore cannot safely hide an expression that may raise an error. This expression can still divide by zero:

IIf([Quantity] = 0, 0, [Total] / [Quantity])

Filter invalid rows first, calculate in a separate query, or use a small VBA function that checks the input before performing the operation.

Null propagation can be useful

Access normally propagates Null through arithmetic. If [Price] is 10 and [Discount] is Null, [Price]-[Discount] is Null. Use Nz only when your business rule truly says that a missing discount means zero.

Microsoft’s IIf documentation explains the evaluation behavior in more detail.