Sumations with Filters

[Ofina]

I have a pre-existing report that is part of a pretty complex Access 97 database. I'm trying to make some changes to this report and I need some guidance.

It is a purchase order report that sums the columns at the bottom. Below this I want to include a small chart that only sums a certain portion of the lines. I presume I need a filter for those boxes somewhere and that's where I'm stuck...on that filter.

In plain English, I want the chart to sum the selling price of all the lines where the customer is not "xxxx" and also where the supplier is not "yyyy".

I have determined that, of course, this chart has to be part of the report footer. Is it possible to include a filter in the Control Source of a Text box? Eg. =Sum([selling price]) where Cust<>"xxxx" Something like that? (I have already tried the above and I know that it's wrong.)

Any assitance would be much appreciated.

 

[dhookom]

Try:
=Sum([selling price] * Abs(Cust<>"xxxx&quot )
Abs() will convert an expression that evaluates to True to the value 1. False expressions will be 0.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Ofina]

Thanks. Logically, that sounds as though it might work. Hopefully I'll get it going properly.

 

[Ofina]

It works! Thanks so much.

Though, one more question. If I want the cust<>"xxxx" to be more like does not contain, what would I use? I don't think that cust<>"*xxxx*" works.

 

[dhookom]

You could try:
=Sum([selling price] * Abs(Instr(Cust,"xxxx")=0) )
If the Cust field contains xxxx anywhere in the field, it will be included in the sum.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.