I am using the following expression in a report and if the SUM comes to zero, I want spaces NOT zero
the expression is: =Sum(([payment 1])+Nz([pmnt2])+Nz([pmnt3]))
I am using the following expression in a report and if the SUM comes to zero, I want spaces NOT zero
the expression is: =Sum(([payment 1])+Nz([pmnt2])+Nz([pmnt3]))
Code:IIF(Sum(([payment 1])+Nz([pmnt2])+Nz([pmnt3]))=0,"",Sum(([payment 1])+Nz([pmnt2])+Nz([pmnt3])))
FYI, you could use the format property of the textbox on the report instead of a formula. Oh, and personally I'd be explicit with the Nz() function: Nz(pmnt2, 0).
Thanks for your answer Joe. That very much looked like the solution but when I copied your code and pasted it in the total field and tried to run the report, I got the error message "Error Extra ) in query expression...etc,etc"
I tried again knocking off the last ')', but got the same result
pair off the brackets to find the missing or extra ones.
Also, be aware it is not a good idea to return either a string or a number - it can cause problems down the line - return null instead of "" for zero or convert your number to a string for consistency
I have tried to pair off all the brackets but cannot find anything wrong, and I'm still stuck with zeros I don't want.
I am a novice , but the suggested expression 'IIF(Sum(([payment 1])+Nz([pmnt2])+Nz([pmnt3]))=0,"",Sum(([payment 1])+Nz([pmnt2])+Nz([pmnt3])))
seems a bit long. Perhaps there is a shorter way of returning spaces in a report if result = zero in this particular sum?
Post # 3 and post # 5.