468,458 Members | 1,826 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,458 developers. It's quick & easy.

Emulate Nz Function

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

Nov 21 '05 #1
4 7153
well you could use the IIF function to simulate the behavior of Nz
regards

Michel Posseth

"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

Nov 21 '05 #2
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

Nov 21 '05 #3
That's very close. The only thing is that zero is valid.

Nov 21 '05 #4
Thanks. That's good code. I wasn't sure how to handle the date issue.

Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by bulk88 | last post: by
61 posts views Thread by /* frank */ | last post: by
7 posts views Thread by Roy Smith | last post: by
11 posts views Thread by TempestV | last post: by
3 posts views Thread by mario.rossi | last post: by
8 posts views Thread by Giovanni R. | last post: by
14 posts views Thread by Eugeny Myunster | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.