How do I SUM only certain orders in form footer?

[Darrell Childress]

I have a continuous subform which shows all orders for a customer. In
the form's footer, I have placed a control which gives a total for all
customer orders... =Sum([OrderAmount])

However, some of the orders have been canceled (denoted by a checkbox
named Cancelled). How can I have the total only include orders which are
NOT canceled? I realize that I could write a query that only displays
uncanceled orders and make that the subform's source, but I would prefer
to show all orders (I've used conditional formatting to show canceled
orders with a red background).
Much thanks,
Darrell

 

[Al Camp]

Darrell,
In the query behind the subform, create a calculated field like this...
(Let's say the field you have your original order price in is called
OrderPrice)
FinalPrice : IIF(Canceled = True, 0, OrderPrice)
Now place the new field, FinalPrice, on your report and sum it. It will
show only the total of non-canceled orders.
You can hide this field in the detail section, and place the sum under
your OrderPrice column so that you can still see all the OrderPrices, but
the sum will only be for non-canceled.

 

[Darrell Childress]

Al,
Great idea. Thanks so much.
Darrell

 

[Steve Schapel]

Darrell,

A variation on Al's excellent suggestion, perhaps neater, would be to
bypass the calculated field in the query, and use this in the COntrol
Source of the textbox in the footer...
=Sum(IIf([Cancelled],0,[OrderAmount])

A further variation, somewhat more obscure, would be this...
=Sum([OrderAmount]*([Cancelled]+1))

 

[Darrell Childress]

Thanks, Steve,
That is a little bit neater approach.
Thanks!