Zero to Null

[Namaste]

Trying to show a blank on a query when the Count() finds
zero. There is a Zn (zero-to-null) function but I can
find no function that will do the reverse. Any ideas?

 

[Allen Browne]

If you just need to suppress the display of the zero on a report, use the
Format property of the text box. Something like this:
#,##0; -#,##0; ""

If you actually need to convert the value to Null (e.g. so any averaging it
not affected by the zero values), you could use this kind of thing:

Function Zero2Null(varIn As Variant)
If varIn = 0 Then
Zero2Null = Null
Else
Zero2Null = varIn
End If
End Function

 

[Van T. Dinh]

In addition to Allen's answer, the correct function name is Nz()
(Null-to-zero or whatever) rather than Zn. There is no Zn function.