> ## 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.

# Working with Null in Microsoft Access
- URL: https://nolongerset.com/null-in-ms-access/
- Published: 2023-08-15T02:40:12.000Z
- Updated: 2026-05-08T12:35:15.000Z
- Description: Let's explore the many ways to check for, handle, and store Null values. Spoiler alert: the best way to do it varies between VBA and SQL.
- Author: Mike Wolfe
- Tags: VBA, Intermediate, #Import 2026-05-20 02:59

*Null is one of many ways to express the concept of nothingness in VBA. For the others, check out [An Article About Nothing](https://nolongerset.com/an-article-about-nothing/).*

---

[Null](https://en.wikipedia.org/wiki/Null%5F%28SQL%29) is a value that basically means "unknown." 

It is especially useful in Access applications because the concept is so integral to relational databases. An optional field in a table has the value `Null` until it is set to something different.

*NOTE: The important role of Null in databases is a topic unto itself and is beyond the scope of this article.*

## Checking for Null

#### In VBA

To explicitly check for Null in VBA, use the [IsNull() function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/isnull-function):

```vba
Sub TestForNull(MyVar As Variant)
    If IsNull(MyVar) Then MsgBox "Passed variable is Null"
End Sub
```

Be aware that T-SQL (the SQL Server flavor of SQL) has its own [ISNULL() function](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql) which operates more like Access's Nz() function (*described below*).

### In Queries

If you are evaluating an expression using the ACE database engine's expression service, then you can also use the `Is Null` *statement* rather than the `IsNull()` *function*. Generally speaking, the `Is Null` statement will perform better than the `IsNull()` function, so you should use it whenever it is available to you. This includes:

- In Queries
- In the **Control Source** property of form and report controls
- Within the `Eval()` function

For more information, refer to this article:

[Expressions vs. CodeWhen is code not code? When it’s an expression. What’s the difference and who really cares? Let’s explore.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2020/10/goats-173940_1280.jpg)](https://nolongerset.com/expressions-vs-code/)

## Handling Null

A common way to handle items that may contain Null is to return a default value if the item is Null.

### In VBA

The easiest way to do this in VBA is with the [**Nz()** function](https://learn.microsoft.com/en-us/office/vba/api/access.application.nz).

```vba
Dim RptDate As Date

'Set RptDate to MyVariant or today's date if MyVariant is Null
RptDate = Nz(MyVariant, VBA.Date())
```

### In Queries

You can use the `Nz()` function in Access queries, but there are a few caveats:

- It only works with Access front-ends (because it's a method of the Access application object)
- It turns dates and numbers into strings
- It's less efficient than the alternative

So, what is the alternative? 

```sql
SELECT Nz(ClosedOn, #12/31/2999#) AS OptionA,
       IIf(ClosedOn Is Null, #12/31/2999#, ClosedOn) AS OptionB
FROM Account
```

As with the `IsNull()` vs. `Is Null` options as described above, you can take advantage of the Jet/ACE expression service to handle all of your Null checking completely within the database engine without having to rely on VBA.

OptionB in the above sample SQL addresses all three caveats of the Nz() function:

- It's standard SQL, so it works natively with most database back-ends, including Access
- It returns the field in its native type, rather than converting to a Variant, so dates and numbers retain their default formatting
- It does not require a round-trip to VBA to evaluate the *Nz()* function, so it executes much faster (this is especially true for linked tables)

The only real downside is that it is less readable than the Nz() version. In some cases, such as when there is no noticeable performance difference between the two versions, the enhanced readability of Nz() is enough of a benefit to make it worth it. However, if you have to optimize a slow-running query to get better performance, this is one of the simplest and most impactful changes you can make.

## Storing Null

### In Database Tables

The value `Null` can only be stored in optional fields.

An optional field in an Access table has the **Required** property set to **No.**

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

A field whose Required property is set to Yes will not accept `Null` values.

Fields in a SQL Server table are declared `NULL` or `NOT NULL` to indicate optional or required, respectively:

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

Required columns in SQL Server Management Studio are identified by their `not null` declaration, while 

Aside from certain special field types, like autonumber or SQL Server rowversion fields, any database type can store Null values. Unfortunately, the same cannot be said for VBA.

### In VBA

The only datatype in VBA that can store `Null` values is the `Variant` data type.

This is too bad, because it means we have to sacrifice type safety to support optional fields. If I want to store the value of an optional datetime field in VBA, I have to use a Variant type to do it. The Variant type can accept both a date and a `Null`, so it will hold all the possible values in an optional datetime field. 

But Variant is "wider" than it needs to be for this purpose.

Not only can that Variant type hold a date and a Null value, it can also hold a number or a string value, too. Other languages, such as C#, have a concept of a "nullable type". 

For example, a [C# nullable DateTime variable](https://www.tutorialspoint.com/chash-nullable-datetime) (`DateTime? dt = null;`) can hold only two types of values:

1. A DateTime value
2. A `Null` value

The nice thing about nullable types is that they more closely reflect how values are stored in a database. Too bad they're not an option in VBA.

#### Making the Best of a Bad Situation

My standard approach to improve readability when declaring VBA variables that will hold optional database field values is to include a comment with the appropriate stricter type:

```vba
Dim ClosedOn As Variant 'Date
```

Depending on the specifics, I also often try to deal with the Null case quickly (perhaps via a [guard clause](https://nolongerset.com/guard-clauses/)) and then store the value in a more strongly-typed variable:

```vba
Dim ClosedOn As Variant 'Date
ClosedOn = DLookup("ClosedOn", "Account", "AccountID=" & AccountID)

Dim IsClosed As Boolean
IsClosed = Not IsNull(ClosedOn)

Dim DateClosed As Date
If IsClosed Then DateClosed = ClosedOn
```

*Cover image created with [Microsoft Designer](https://designer.microsoft.com/)*

***UPDATE*** *\[2023-09-06\]: Added introductory paragraph with a link to [An Article About Nothing](https://nolongerset.com/an-article-about-nothing/).*