Calculate based on check box

[Guest]

I have a form called "Investments" and then a subform set up to record
detailed investment transactions for each investment. I would like to add
the "Investment cost" field in the footer of the subform based on only those
detailed transactions with no check in the check box for "replaced/sold"
(which means they are still active investments).

Normally I would just set up an unbound text box and use
=sum([InvestmentCost]) to calculate that field, but how do I bring into this
formula only those with no check?

I am new to access...so I hope I explained this clearly enough.

 

[Bob Quintal]

I have a form called "Investments" and then a subform set up
to record detailed investment transactions for each
investment. I would like to add the "Investment cost" field
in the footer of the subform based on only those detailed
transactions with no check in the check box for
"replaced/sold" (which means they are still active
investments).

Normally I would just set up an unbound text box and use
=sum([InvestmentCost]) to calculate that field, but how do I
bring into this formula only those with no check?

I am new to access...so I hope I explained this clearly
enough.

The checkbox has a value of -1 when checked and 0 when not checked.
if we add 1 to that we get 0 if checked and 1 if unchecked , which
you can use to multiply by the amount in the investment cost field,
Since multiplication by 0 is zero, you essentially don't add the
checked items

=sum([InvestmentCost]*([replaced/sold]+1))
should work in your unbound textbox.

 

[Guest]

=Sum(IIF([replaced/sold]=0,[InvestmentCost],0))

 

[Guest]

This only produced $0.00 on all of them regardless of the check box. Any
other suggestions? Should I do the calculation in the query?

 

[Guest]

Never mind...I deleted the text box and re-created and this works. Thanks!