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
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"
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).
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
For more information, refer to this article:
A common way to handle items that may contain Null is to return a default value if the item is Null.
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())
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
As with the
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.
In Database Tables
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
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.
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:
- A DateTime 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.