Suppress #Error in calculated field

Question

Sorry, my friend: No joy using Nz() or its less efficient cousin, IIF(), when I use them as a text field's control source. Any other ideas?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Re: Suppress #Error in calculated field
Date: 2 June 1999 14:18

Greg,

I advised you to use NZ(...) but I never used it ;-) Since Acc2.0 times I succefully use the following my function to suppress #error in calculated fields:

Public Function smsNullToZero(ByVal vvarAnyValue As Variant) As Variant
' Purpose:
'       Convert Null value to zero (variant) if it is equal to Null.
'       Return input value if it is not Null.
' Arguments:
'       vvarAnyValue - value to check for Null and convert to zero if it is
Null
' Returns:
'       0 (variant Zero) - if input value is Null,
'       Input value    - if it isn't Null
' Authors:
'       SMS, 27/06/97
' Example:
'       varRet = smsNullToZero(Null)
' Calls:
'
' Revisions:
'
    On Error GoTo smsNullToZero_Err

    If IsNull(vvarAnyValue) Then
        smsNullToZero = 0
        Exit Function
    ElseIf Trim(vvarAnyValue) = "" Then
        smsNullToZero = 0
    Else
        smsNullToZero = vvarAnyValue
    End If

smsNullToZero_Exit:
    Exit Function
smsNullToZero_Err:
    smsNullToZero = 0
    Resume smsNullToZero_Exit
End Function

Feel free to adapt it to your needs,
HTH,
Shamil