We help IT Professionals succeed at work.

Need to build an Iif statement based on values coming up 0 to not show.

stephenlecomptejr
on
Need to build an Iif statement based on values coming up 0 to not show.

I have the following control source in a text box:

=Sum(Nz([01WorkHours],0))

Open in new window


But there are some values that come up 0.
I want to change this to where if it's 0 - then come up blank even thought it's a numeric field.

I know I can use Iif statement to make this work but having trouble with the syntax:
I used this and get the #error

iif(Sum(Nz([01WorkHours],0)) = 0, Null)

How do I change the above to make it work?

Thank you in advance.
Comment
Watch Question

Paul Cook-GilesSenior Application Developer

Commented:
=iif(Sum(Nz([01WorkHours],0)) = 0, "")
NorieAnalyst Assistant

Commented:
Have you tried using a custom format for the field/textbox?

You could try something like this to show 0s as blank.

0.00;-0.00;""
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You miss the second condition parameter:

=IIf(Sum(Nz([01WorkHours],0))=0,Null,Sum(Nz([01WorkHours],0)))

Open in new window