Nz() function does not work with Excel

[Maximnl]

Hallo guys,

I am stuck with a simple fact. Nz() function works in Access queries. When I try to import data via these queries into Excel, it says : Nz is not recognized. So anyone knows a good replacement for Nz() that works in Excel as well?

With kind regards
Maxim

P.S. I use office 2003.

 

[Maximnl]

IIF function works fine

IIF function does the same job as Nz() and queries with it can be
used in Excel.

BRT+If([EXP]<>0;[EXP];0)

 

[Dennisk]

develop an excel function (if such a thing is possible)

Public Function Nz(byVal Value as Variant) as Variant
if isnull(Value) then return 0
return Value

PS the Above should read
IIF([EXP] is Null, 0, [EXP])

if use in a query

Although it does work you are testing for null and not 'Not zero'

 

[bephraim]

Here is an easy solution, it works just like in Access


Public Function Nz(Value1 As Variant, ifNull As Variant)

If Value1 = "" Then
Nz = ifNull
else
Nz = Value1
End If

End Function

 

[ByteMyzer]

Hello, bephraim,

You're solution will not work if Value1 truly is Null.

Try this function in Excel instead:

[COLOR="Navy"]Public Function[/COLOR] Nz([COLOR="Navy"]ByVal[/COLOR] Value, [COLOR="Navy"]Optional ByVal[/COLOR] ValueIfNull = "")

    Nz = IIf(IsNull(Value), ValueIfNull, Value)

[COLOR="Navy"]End Function[/COLOR]