expression that excludes specified records

[Slez via AccessMonster.com]

I have a control called txtTotalMatCost that provides a total in the report
footer.
It's Control Source is set to: =Sum([LineMaterialCost])
It works properly in its current state, but...
I would like it to total LineMaterialCost for all records that are not
related to "CBDCodeID" where CBDCodeID = 14.

I have 2 thoughts of how to achieve this:
1. Write the expression in txtTotalMatCost to exclude records where
CBDCodeID = 14.
2. Create another control that totals just records where CBDCodeID = 14, and
subtract that from txtTotalMatCost.

If either way is a good approach, I still am uncertain of how to write the
expression. Can anyone tell me if this reasonably possible and offer some
guidance with an expression?
Feedback is greatly appreciated!
Slez

 

[Marshall Barton]

I have a control called txtTotalMatCost that provides a total in the report
footer.
It's Control Source is set to: =Sum([LineMaterialCost])
It works properly in its current state, but...
I would like it to total LineMaterialCost for all records that are not
related to "CBDCodeID" where CBDCodeID = 14.

I have 2 thoughts of how to achieve this:
1. Write the expression in txtTotalMatCost to exclude records where
CBDCodeID = 14.
2. Create another control that totals just records where CBDCodeID = 14, and
subtract that from txtTotalMatCost.

If either way is a good approach, I still am uncertain of how to write the
expression. Can anyone tell me if this reasonably possible and offer some
guidance with an expression?

Doing it in the footer text box is easiest:

=Sum(IIf(CBDCodeID = 14, 0, LineMaterialCost))

 

[Slez via AccessMonster.com]

That works perfectly! Thank you!
Slez

I have a control called txtTotalMatCost that provides a total in the report
footer.

[quoted text clipped - 12 lines]

expression. Can anyone tell me if this reasonably possible and offer some
guidance with an expression?

Doing it in the footer text box is easiest:

=Sum(IIf(CBDCodeID = 14, 0, LineMaterialCost))