> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# T-SQL ISNULL() vs. VBA IsNull() vs. Jet IS NULL vs. Access VBA Nz()
- URL: https://nolongerset.com/null-handling-in-ms-access/
- Published: 2022-04-16T02:55:21.000Z
- Updated: 2026-05-08T12:57:37.000Z
- Description: How do I check thee for NULL? Let me count the ways.
- Author: Mike Wolfe
- Tags: SQL, Intermediate, SQL Server, #Import 2026-05-20 02:59

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](https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-ver15):

> Replaces NULL with the specified replacement value.

Syntax: `ISNULL ( check_expression , replacement_value )`

---

[The Nz() function](https://support.microsoft.com/en-us/office/nz-function-8ef85549-cc9c-438b-860a-7fd9f4c69b6c):

> 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](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/isnull-function):

> Returns a **Boolean** value that indicates whether an [expression](https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#expression) contains no valid data ([Null](https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#null)).

Syntax: `IsNull(*expression*)`

---

Jet [IS \[NOT\] NULL statement](https://nolongerset.com/expressions-vs-code/#implications):

> 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`

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/04/image-54.png)

You can use the `Access.Application.Eval()` function to force the Jet/ACE expression service to evaluate a string. As you can see here, the result of the `Is Null` comparison is returned as a `0` or `-1`.

---

### 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~~](https://nolongerset.com/iif-function-vs-statement/) [operator](https://nolongerset.com/ternary-operator-iif/) so it uses efficient short-circuiting boolean evaluation
- `IIf` is built-in to the [Jet Expression Service](https://nolongerset.com/expressions-vs-code/), 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](https://nolongerset.com/iif-is-null-vs-nz-testing-a-hypothesis/).*

### 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](https://pixabay.com/users/antoniusales-35941/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=517839) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=517839)*