Alternative to Nz for Access v1 / v2

        4 votes: *****     3,914 views      No comments
by Allen Browne, 20 April 2005    (for Access v1, v2)

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: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour 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)

Comments require approval before being displayed on this page (allow 24 hours).