Solved

Suppressing zero display in a field

Posted on 2014-02-25
4
241 Views
Last Modified: 2014-02-25
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
0
Comment
Question by:rltomalin
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39885278
Is the field formatted as a numeric field?  

Try this:

=IIf([Actual supervisor hours] >0 ,[Actual supervisor hours],NULL)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39885290
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.
0
 

Author Closing Comment

by:rltomalin
ID: 39885318
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
0
 
LVL 57
ID: 39885404
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now