By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,168 Members | 2,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,168 IT Pros & Developers. It's quick & easy.

Emulate Nz Function

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
That's very close. The only thing is that zero is valid.

Nov 21 '05 #4

P: n/a
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.