How do I Filrer Minus Sums Within a Report

[John A]

Hi All,

In a report, based on a standard query, I want to list Sales Ledger invoices
and credit notes, grouped by customer name and raised in a selected month,
but not display a customer balance (sum) for the month if it is a negative
value i.e. if they have more credit notes than invoices by value.

I can list the customers invoices/credits individually and derive the sum
value for that customer without a problem although I don't know whether
this would be more appropriate to achieve within the underlying query or the
report itself. Zero balances are not a problem.

I apologise if this has been asked a million times before but I find
newsgroup searching an imprecise science at best. On that subject, anybody
know of a really good newsgroup search utility?

Appreciate any help you could give me.

Thanks,

John

 

[Marshall Barton]

In a report, based on a standard query, I want to list Sales Ledger invoices
and credit notes, grouped by customer name and raised in a selected month,
but not display a customer balance (sum) for the month if it is a negative
value i.e. if they have more credit notes than invoices by value.

I can list the customers invoices/credits individually and derive the sum
value for that customer without a problem although I don't know whether
this would be more appropriate to achieve within the underlying query or the
report itself. Zero balances are not a problem.

I apologise if this has been asked a million times before but I find
newsgroup searching an imprecise science at best. On that subject, anybody
know of a really good newsgroup search utility?

A text box in a group or the report footer can sum the
detail values:
=Sum(invoiceamount)

To display nothing for negative values, set the text box's
Format property to a custom format that doesn't use the
negative format. Perhaps something like:

$#,##0.00;"";0.00