If you've done much VBA programming in Microsoft Access, you've probably come across the "Invalid Use of Nulls" error. It's probably the most common error that I deal with in Access applications. Let's take a look at what it means and how to deal with it.
Are Nulls the Same as Spaces?
If you have a vague understanding of nulls, you may think of them as spaces, but that's not accurate. Nulls are not spaces. Null are not letters or numbers. Nulls are not values at all. They are not even empty strings.
Nulls are a database indicator that a field does not have a value. It's kind of like a placeholder, where a value will exist at some point in the future.
If there was a space in the field, that would be a value, not a null.
If there was a zero in it, that would also be a value, not a null.
You can also have an empty string - a field which has nothing in it - but is not null.
Confused?
If you're having trouble following this train of thought, welcome to the club. Nulls are always hard to understand.
Let's look at an example to clarify.
I once managed a database for my local PTO (Parent-Teacher Organization). In the Members table, I had a field called "ChildrenLivingAtHome", where we'd enter the names of all kids that lived in the household. It was voluntary information - some parents provided it and others didn't. Let's see an example of what that might have looked like:
Notice that the children aren't listed for the last two records. They look blank, but in fact, they are null. How do I know? I wrote a little bit of VBA code to find out:
When this code runs, it successfully prints out the parent/child info for the first two records, but fails on the third record:
As you can see, VBA doesn't like the null values on the third record. If it was spaces, the program would be fine. But because its null, VBA can't handle that.
How to Handle Nulls
One of the easiest ways to handle Nulls in your VBA code is to use the NZ function. I believe "NZ" stands for "Null to Zero", and as the name implies, it was originally designed to transform null values into zeroes. It's even better than that now, though, allowing you to replace a null values with pretty much anything: an empty string, a text string, a specific number, a default date - whatever you need.
I'll alter my code to replace nulls with an empty string:
Now when I run the code, there is no error message, and my result looks like this:
That's better, but I can take this a step further. Rather than leaving the children's info blank, I'm going to print "Unknown", simply by telling the NZ function to replace nulls with the text "Unknown":
As you can see, I changed the second argument of the NZ function to "Unknown". This is how I tell Access to replace any nulls with the word "Unknown". Then, when I print the result in the debug window, I see "Unknown" for the last two entries.
Works in Queries Too
One of the best things about the NZ function is that you can use it in queries too. Here's an example:
NZ is a useful function, and the one I use most often when working with Access.
I'll cover nulls in more detail in my next Access VBA post. Subscribe using the form below to be notified when that post goes live.
If nulls (or other bad data) are causing you headaches in your Access database, reach out to start a conversation about it. I'm available to jump in and correct issues with your data or alter your VBA code to get around them. No project is too small - if you just need an hour of my time to fix a couple quick issues, that works for me. Bigger projects are, of course, always welcome too.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2023. All rights reserved.