Replace result of 0 with empty string (null)

[Laura C.]

I have a calculated field in a query that calculates a sum based on whether
or not another field = "Yes" or "No."

It just so happens that currently when I run the query, one of the
calculated results is "0." But in general, our protocol for the report that
runs off the query is to show a null value if the individual in question has
no shares of that type. Prior to the most recent transactions, we never had
an individual simply zero out his shares.

The old expression in the query reads like this:

NonVoting:
Sum(IIf([Transactions]![Voting/NonVot]="No",[Transactions]![Shares]))

I basically need a new calculated field where multiple conditions are at
play, i.e., if [Transactions]![Voting/NonVot]="No" AND the sum is 0, return a
null value; if the [Transactions]![Voting/NonVot] = "No" AND the sum is
greater than 0, add up the shares.

I've tried every permutation I could think of and nothing is working. Can
you help?

 

[John W. Vinson]

I've tried every permutation I could think of and nothing is working. Can
you help?

I'd suggest a sneaky trick: use the four-value Format property of a textbox on
the report to *display* a 0 as blank. The Format property for a number field
can have four formats separated by semicolons, for positive, negative, zero
and NULL values respectively; so try

"#";"-#";"",""

to display positive numbers as 424, negative as -38, and zero and blank as an
empty string.

 

[Laura C.]

Thanks, John! That was a sneaky and deceptively simple solution.

Just for others' reference, I had to tinker a bit with the format to make it
come out right. In the end, the quotation marks around the # signs had to go.
The following went into the Format property of my Textbox:

#;-#;""

It now works like a dream.