Navigation:  Articles > Mar-1997 >

Nulls or Zero Length Strings - Doesn't Matter

Previous pageReturn to chapter overviewNext page

Paul Litwin              

 

                  ad7468x60                                          

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 

End If

 

A better way to do this is:

 

‘ More efficient 

If Len(varAge & "") = 0 Then

     ‘ do something 

End If

 

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 = "" 

Else     

     strAge = txtAge 

End If

 

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