Try this and see if it is what you are looking for. I took some liberties
as to what NZ does, as I do not have Access and the documenation online
(that I could find) wasn't too clear.
I find that IIF is cumbersome when evaluating multi-step operations such as
IIF(((Value is Nothing) orelse (isnumeric(Valu e) andalso
Value=0)),SomeN ewValue,Value)
Finally, this will only work at the code level, and you cannot use this
function in SQL like you could have in Access.
Dim b As Object
Dim s As String
Dim y As Decimal
Dim x As Date
Debug.WriteLine ("Fails NZ Test")
Debug.WriteLine (String.Format( "Object:-{0}-", AccessHelperFun ctions.NZ(b,
CType("", String)).ToStri ng))
Debug.WriteLine (String.Format( "String:-{0}-", AccessHelperFun ctions.NZ(s,
CType("", String)).ToStri ng))
Debug.WriteLine (String.Format( "Decimal:-{0}-", AccessHelperFun ctions.NZ(y,
CType("", String)).ToStri ng))
Debug.WriteLine (String.Format( "Date:-{0}-", AccessHelperFun ctions.NZ(x,
CType("", String)).ToStri ng))
Debug.WriteLine ("")
Debug.WriteLine ("")
Debug.WriteLine ("Passes NZ Test")
Debug.WriteLine (String.Format( "Object:-{0}-", AccessHelperFun ctions.NZ(New
DataSet, CType("", String)).ToStri ng))
Debug.WriteLine (String.Format( "String:-{0}-",
AccessHelperFun ctions.NZ("Some TextHere", CType("", String)).ToStri ng))
Debug.WriteLine (String.Format( "Decimal:-{0}-",
AccessHelperFun ctions.NZ(15.67 89, CType("", String)).ToStri ng))
Debug.WriteLine (String.Format( "Date:-{0}-", AccessHelperFun ctions.NZ(New
DateTime(12, 3, 4), CType("", String)).ToStri ng))
Debug.WriteLine ("")
Debug.WriteLine ("")
Debug.WriteLine (String.Format( "NZ Uses This Application:-{0}-",
AccessHelperFun ctions.NZUseCou nt.ToString))
Results:
Fails NZ Test
Object:--
String:--
Decimal:--
Date:--
Passes NZ Test
Object:-System.Data.Dat aSet-
String:-SomeTextHere-
Decimal:-15.6789-
Date:-3/4/0012 12:00:00 AM-
NZ Uses This Application:-8-
Code:
Public Class AccessHelperFun ctions
Private Shared m_NZUseCount As Decimal
Shared Sub New()
m_NZUseCount = 0
End Sub
#Region " NZ "
Public Shared Function NZ(ByVal Value As Object) As Object
Return (NZ(Value, True, Nothing))
End Function
Public Shared Function NZ(ByVal Value As Object, ByVal FailOnDefaultDa te
As Boolean) As Object
Return (NZ(Value, FailOnDefaultDa te, Nothing))
End Function
Public Shared Function NZ(ByVal Value As Object, ByVal ValueWhenNull As
Object) As Object
Return (NZ(Value, True, ValueWhenNull))
End Function
Public Shared Function NZ(ByVal Value As Object, ByVal FailOnDefaultDa te
As Boolean, ByVal ValueWhenNull As Object) As Object
'
' Tests if a given value is Null or Zero
' If true return the ValueWhenNull
' If false return the Value
'
' Optionally, test for default date values
'
'Usage counter variable, omit if you want
NZUseCountIncre ment()
'Check if the value is null or zero
If Value Is Nothing OrElse (IsNumeric(Valu e) AndAlso Value = 0) Then
'Return the ValueWhenNull
Return ValueWhenNull '"Nothing"
'Check if the value compares to System.DBNull, this should never happen
ElseIf Value Is System.DBNull.V alue Then
'Return the ValueWhenNull
Return ValueWhenNull '"System.DBNull "
'Check if this is a date, that we are to check for min date values, and
it is the min value
ElseIf FailOnDefaultDa te AndAlso (IsDate(Value) AndAlso Value =
Date.MinValue) Then
'Return the ValueWhenNull
Return ValueWhenNull '"System.Date.M inValue"
'Check if this is a string and if it is set to String.Empty, we should
never get to here
ElseIf (TypeOf Value Is String) AndAlso (CType(Value, String) Is
String.Empty) Then
'Return the ValueWhenNull
Return ValueWhenNull '"String.Emp ty"
Else
'Return the Value
Return Value
End If
End Function
Private Shared Sub NZUseCountIncre ment()
' Increments the number of times
' NZ was called
'
m_NZUseCount += 1
End Sub
Public Shared ReadOnly Property NZUseCount() As Decimal
' Returns the number of times NZ was called
' this value is over the application lifetime
' as shared objects are not unloaded once loaded
Get
Return m_NZUseCount
End Get
'
End Property
#End Region
End Class
"Paul" <pw****@hotmail .com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...
Anyone have code that emulates the Nz function in Microsoft Access?
In Access it is:
Nz(Value as variant, Optional ValueIfNull as Variant) as Variant
