Working with Null in Microsoft Access

Null is one of many ways to express the concept of nothingness in VBA.  For the others, check out An Article About Nothing.

Null 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:

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

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.

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?  

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.

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

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 (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:

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) and then store the value in a more strongly-typed variable:

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

UPDATE [2023-09-06]: Added introductory paragraph with a link to An Article About Nothing.