Replace zeros with "-" in Reports

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hadi - 20 Jul 2007 18:50 GMT

Hello Experts,

I have a report that based on a query. The query adds up my monthly hours
for the people that work on the project and the report displays the result.
my formula in the query to calculate total forJanuary for example is 07Jan:
Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4]). So all I do is add
the field 07Jan into my report. The problem am having is I like to replace
all the 0.00 totals with "-". I try to do IIF(Nz([07Jan]=0,"-",Nz([07Jan])
but I get an #Error in the repot. The only way I got to work is by using a
text box and if I repeat the whole formula in the report as well by saying:
IIF(Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4])=0,"-",Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4]))
but I am defeating the point of using the query to calcuate my totals. I am
sure there is a simple way to do it but can't get there

Help is always appreciated

Hadi

Reply to this Message

Hadi - 20 Jul 2007 19:06 GMT

Never mind. I figured out what I was doing wrong. I was typing the IF
statement in the field box itself not a seperate text box.

thanks anyways

> Hello Experts,
>
[quoted text clipped - 13 lines]
>
> Hadi

Reply to this Message

JK - 21 Jul 2007 00:59 GMT

Hadi,

Yes, there is an easier way.

Use the format property which does not change the value of a numeric field -
only the way it looks. There are four sections separated by a semi-colon,
Positive;Negative,Zero;Null. Suppose you want negative numbers to be
enclosed in brackets and dash instead of 0 - set the Format property of the
field on the report to:

0.00;(0.00);"-"

Regards/JK

| Hello Experts,
|
[quoted text clipped - 6 lines]
| but I get an #Error in the repot. The only way I got to work is by using a
| text box and if I repeat the whole formula in the report as well by saying:

IIF(Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4])=0,"-",Nz([07Jan_1])+Nz([07Jan_2])+Nz([07Jan_3])+Nz([07Jan_4]))

Reply to this Message