John Skolits
askolits at ot.com
Fri Jun 22 06:32:03 CDT 2007
I've been loosely following this thread. I was having problems with NZ many
years ago and came up with the below. It may not be as efficient as it could
be and some of you may find some holes in the programming logic but it has
'never' failed me. I've used it for ten years.
John Skolits
Function N2Z(anyValue As Variant) As Double
''************************************************************************
'' CDD Inc Procedure Identification
''------------------------------------------------------------------
'' FUNCTION: N2Z
''
'' PURPOSE: Used through the application,
'' it converts null and empty values to zero
''
'' ARGUMENTS: anyValue - any value to check
''
'' RETURNS: Zero for empties, nulls and #deleted# items
''
''
'' Date/AUTHOR: John Skolits 03/14/97
''************************************************************************
10 On Error GoTo N2Z_ERR
'*********BEGIN CODE HERE ********
20 On Error GoTo N2Z_ERR
30 If anyValue = "#Deleted" Then anyValue = Null
40 If IsNull(anyValue) Or IsEmpty(anyValue) Then
50 N2Z = CDbl(0)
60 Else
70 N2Z = CDbl(anyValue)
80 End If
N2Z_EXIT:
90 Exit Function
N2Z_ERR:
100 If Err = 13 Then Resume N2Z_EXIT 'Display the error
110 If Err = 3021 Then
120 MsgBox "You are trying to use the N2Z functionwith no data.",
vbInformation, "N2Z error"
'(Note: This message may appear multiple times.)
130 Resume N2Z_EXIT
140 End If
150 If Err = 2427 Or Err = 2424 Or Err = 63933 Then
160 N2Z = CDbl(0)
170 Resume N2Z_EXIT
180 End If
Dim strCallingObject As String
190 strCallingObject = "N2Z" & " " & Application.CurrentObjectName & "
Line: " & Erl
200 MsgBox Err, Error, strCallingObject
210 Resume N2Z_EXIT
End Function
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com