I've got a simple select query as the basis of a report with multiple
currency fields and a few text fields.
If all of the currency fields are zero I would like to exclude them from the
report. There are 17 currency fields.

How can I amend the query to exclude those empty rows?

buckskin wrote:

Well, you could have a calced field in your query. Ex:
TotCur : [Field1] + [Field2] + [Field3]....

And in the criteria have
<> 0

I think that should work.

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote

Oooh, that's clever. Thanks.

buckskin wrote:


You're welcome.

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote

Indeed! Just got it working. Super sweet.

buckskin wrote:

Uhhhh...potential problem. If there are negative numbers there is a
possibility that adding up numbers could equal zero. Adding Abs()
around each field in the expression would solve that if negatives are
possible. Or a humoungous criteria statement with ORs would work as well.

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote

Yes negatives can and will happen. I don't know what Abs() is but will
lookup.

I was playing with criteria but at some point you can't go to the next
field - did I run out of them? i.e there's a limit to the criteria you can
specify in Access 2000?

buckskin wrote:

negative. Thus Abs(3) + Abs(-1) + Abs(-2) would be 6, not zero.

That I would not know. I suppose you could ViewSQL and write in the
code lines and see if it works if you can't specify it in from the
designer window.