Suppressing zero display in a field

rltomalin
rltomalin used Ask the Experts™
on
I am using the following in the Control Source of a text box:

=IIf([Actual supervisor hours]>0,[Actual supervisor hours],"")

What I want to do is have the field blank rather than displaying zero if the value is zero.

This displays #Error in the field.  What am I doing wrong.

Regards

Richard
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
Is the field formatted as a numeric field?  

Try this:

=IIf([Actual supervisor hours] >0 ,[Actual supervisor hours],NULL)
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Also, make sure that the *textbox* name property is not set to "Actual supervisor hours".

If that is the name of the underlying field, name the textbox something like "txtActualSupervisorHours" to make it distinct and to avoid errors from circular references.

Author

Commented:
Hi mbizup

Thanks again.  Yes it is a numeric textbox and it was named the same as the field.  So after I put both of those right it was fine.

Regards

Richard
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Richard,

  Just some additional comments:

1. When you use a field reference like that, you will still see #Error if the underlying record source has no records.

2. You can also use the Format property to control how a numeric is displayed.  A format specification can be comprised of up to four sections.   First is for positive numbers, second negatives, third zeros, and forth Nulls.

  If you don't use a section, then it defaults to the first specified.  For example:

"$#,##0;;\Z\e\r\o"

  Specifies a first and third value, so positives and negatives use the $#,##0 specification, and zeros would display as 'Zero'

HTH,
Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial