EVEN though Access considers Nulls and zero-length strings (ZLS) separate values, often developers wish to treat them as equivalent for the sake of an If...Then statement or some other stretch of code. One way to accomplish this is to write code like this:
‘ Not so efficient
If IsNull(varAge) or varAge = "" Then
‘ do something
A better way to do this is:
‘ More efficient
If Len(varAge & "") = 0 Then
‘ do something
This second version of the code is more efficient for two reasons. First, if varAge is Null, the code converts it into a ZLS by appending a ZLS to it. This lets us replace two tests with one. Second, it’s simply more efficient to check for a ZLS using Len(ZLS) = 0 rather than ZLS="".
You can use a variation of this technique if you ever need to place the value of a text box or a variant variable into a string. Strings can’t contain Null values, so you may be tempted to write code like this that first checks to see if the text box is Null:
‘ Another correct but slow method
If IsNull(txtAge) Then
strAge = ""
strAge = txtAge
A much faster way to do this is to use code like this instead:
‘ This is faster
strAge = txtAge & ""
This code avoids doing the test altogether by setting the value to the concatenation of the text box and a ZLS. If the text box is Null, this converts it into a ZLS. If the text box is either a ZLS or a regular value, then the concatenation has no effect.
Read more on Nulls in Peter Vogels editorial here Not For Null
Or check out the official Access blog on this Nulls article