Replace zeros with "-" in Reports

[Guest]

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

 

[Guest]

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

 

[JK]

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,
|
| 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