Running Sum of only certain records

[gschimek]

I have a report that displays details about insurance policies, including the monthly cost of each. It lists a person's name and all the policies that each person owns, and then totals up the monthly cost.

I want to change the "Total" slightly. Right now it does a Sum of the values in the [Premium] field. But when someone cancels one of their policies, I still want it displayed on the report, but not included in the sum.

In other words:

Jim Smith
Policy 1 - Active - $20.00
Policy 2 - Active - $15.00
Policy 3 - Canceled - (cost not displayed, but still stored in table as $10.00)
Total: $35.00​

Right now, because the cost of the canceled policy is still stored but not displayed, the monthly total would calculate as $45.00. And the policy is categorized as Canceled when there is a date stored in a [CanceledDate] field in the table.

What do I need to do to keep those canceled policies from being included in the total?

Thanks in advance.

 

[pbaldy]

Try

=Sum(IIf(IsDate(CanceledDate), 0, Premium))

 

[gschimek]

Perfect! I knew there had to be an easy answer. Thanks.