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(Value) andalso
Value=0)),SomeNewValue,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}-", AccessHelperFunctions.NZ(b,
CType("", String)).ToString))
Debug.WriteLine(String.Format("String:-{0}-", AccessHelperFunctions.NZ(s,
CType("", String)).ToString))
Debug.WriteLine(String.Format("Decimal:-{0}-", AccessHelperFunctions.NZ(y,
CType("", String)).ToString))
Debug.WriteLine(String.Format("Date:-{0}-", AccessHelperFunctions.NZ(x,
CType("", String)).ToString))
Debug.WriteLine("")
Debug.WriteLine("")
Debug.WriteLine("Passes NZ Test")
Debug.WriteLine(String.Format("Object:-{0}-", AccessHelperFunctions.NZ(New
DataSet, CType("", String)).ToString))
Debug.WriteLine(String.Format("String:-{0}-",
AccessHelperFunctions.NZ("SomeTextHere", CType("", String)).ToString))
Debug.WriteLine(String.Format("Decimal:-{0}-",
AccessHelperFunctions.NZ(15.6789, CType("", String)).ToString))
Debug.WriteLine(String.Format("Date:-{0}-", AccessHelperFunctions.NZ(New
DateTime(12, 3, 4), CType("", String)).ToString))
Debug.WriteLine("")
Debug.WriteLine("")
Debug.WriteLine(String.Format("NZ Uses This Application:-{0}-",
AccessHelperFunctions.NZUseCount.ToString))
Results:
Fails NZ Test
Object:--
String:--
Decimal:--
Date:--
Passes NZ Test
Object:-System.Data.DataSet-
String:-SomeTextHere-
Decimal:-15.6789-
Date:-3/4/0012 12:00:00 AM-
NZ Uses This Application:-8-
Code:
Public Class AccessHelperFunctions
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 FailOnDefaultDate
As Boolean) As Object
Return (NZ(Value, FailOnDefaultDate, 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 FailOnDefaultDate
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
NZUseCountIncrement()
'Check if the value is null or zero
If Value Is Nothing OrElse (IsNumeric(Value) 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.Value 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 FailOnDefaultDate AndAlso (IsDate(Value) AndAlso Value =
Date.MinValue) Then
'Return the ValueWhenNull
Return ValueWhenNull '"System.Date.MinValue"
'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.Empty"
Else
'Return the Value
Return Value
End If
End Function
Private Shared Sub NZUseCountIncrement()
' 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*********************@f14g2000cwb.googlegro ups.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
