Alternative to Nz for Access v1 / v2

        4 votes: ****      4,023 views      0 comments
by Allen Browne, 20 April 2005    (Access 1, 2)

Null2Zero(), StrLen()

These two functions are very simple but useful in Access 1 and 2, since Nulls propagate like rabbits. From Access 95 onwards, use the built-in Nz() function.

Use Null2Zero() to prevent runtime errors when assigning values to non-variants, or to prevent calculations choking over Nulls.

Function Null2Zero(AValue)
   ' Purpose: Return the value 0 if AValue is Null.
   If IsNull(AValue) Then
      Null2Zero = 0
      Null2Zero = AValue
   End If
End Function

Len() returns Null as the length of a Null variant, so StrLen() is a quick substitute. Since any non-zero value = True, you can test for no entry (Null or zero length string) with If Not StrLen([MyControl]) Then

Function StrLen(AVariant) As Integer
   ' Returns the length of a variant or string, treating Null as a zero-length string.
   If IsNull(AVariant) Then
      StrLen = 0
      StrLen = Len(AVariant)
   End If
End Function

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent

Have your say - comment on this article.

What did you think of 'Alternative to Nz for Access v1 / v2'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)
Verify Code
Verification Code

It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.