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
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?
Fortunately, Access has a built in function for testing a variable for Null.
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.
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
a simpler solution may be to use the IsNull function to test a field, for example,
If IsNull(x) then
Absolutely correct! This is one of those functions I found out about after taking measures to work around with a function, hence the article Code I should have used.
The only possible advantage of my little function is that it will also trap IsMissing variables as well as null, but that’s an admittedly tiny chance.