asked on

Suppressing zero display in a field

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

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

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.

rltomalin

ASKER

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)

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.