VBA Won’t let me test for NULL in an If statement!

Seems like it would be pretty simple right?  Just make an If statement like this:

If x=Null Then

‘Blah blah blah

End If

But alas, this bit of code will never be run, even if the variable x is actually Null.  Using the syntax “x Is Null” doesn’t work either, so how can we test for a Null value variable in order to run an If statement and trigger some action?

Workaround

Fortunately, Access has a built in function for testing a variable for Null.

IsNull(x)

That simple function will return TRUE if the pass variable is a null, at which point you can use an If statement to alter the code path.  Alternatively, you can use this little gem to substitute a value when your variable is null without an If statement.

Nz(x,”whatever shouldn’t be null”)

Using this function, if x is not a null the result will be whatever X was to begin with, but if it is a null, the function will return the second argument, in this case “whatever shouldn’t be null as a string.  Use a number or date there as well, depending on what you’re trying to populate.

Variable Types

Not all variable types can be null, in fact the only one that can is the Variant.  If you’re declaring your variables by type, they’ll be whatever the default value is before they’re set instead of a null.  For example:

• string = “”
• Integer, double, float, date (pretty much any numeric type) = 0
• boolean = false