Nulls vs. zero-length strings: a Power Tip from the past

Today's guest blogger is Access MVP Garry Robinson, who offers the Smart Access collection of articles at http://www.vb123.com/kb.

A few months ago I purchased the rights to publish the Smart Access magazine online and it is from that collection that I offer this tip from Paul Litwin, the first editor of Smart Access. At the end of that I provide a link to an editorial on Null Values written by the second editor Peter Vogel. It never hurts to get a refresher course on Null values, a necessary evil for database developers.

Tip by Paul Litwin from 1997

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 http://www.vb123.com/kb/200207_pv_ed.htm