Calculating totals in a form based on check boxes.

[con10t]

Good morning! I am a newbie, so please bear with me. I am creating a form that lists the costs associated with a candidate. Some of these costs we pass on to our clients, some costs we pay for. I have a subform that allows the user to list each item (travel, test materials, off-site office space, etc.) separately and fill in a dollar amount. I am able to give our accountant the total costs related to the candidate simply by using =Sum([CostAmount]). But she would like to be able to also have an OurExpense check box. If this box were checked that amount would be subtracted from the total; then she could invoice the bottom line. How do I create an expression for that or do I need to create a query? Thanks, in advance for all your help! DC

 

[PWise]

=dlookup("CostAmount","tablename","OurExpense=false&quot

 

[kewo99]

Try this:
=Sum(IIF(cbOurExpense,[CostAmount],0))

Ken

 

[con10t]

Thanks for trying. I must be typing something in incorrectly. When I try pwise's suggestion the total in the Billable Expense is always $15.00 regardless of which candidate I am viewing--some have no costs, others have hundreds of dollars.

When I try kewo99's suggestion, both the Total box and the Billable Expense box has a #Error in it. (If I make the Billable Expense box Unbound, then the Total box functions fine again.)

(The totals are transferred from the subform to a form where they can be viewed)

Can either of you tell me what I am doing incorrectly? I'm willing to tell the accountant to use the check box to alert her to what to deduct and deduct it manually, if you'd rather not bother. Thanks, again, for your help!

 

[kewo99]

pwise's answer was closer than mine.
I think this will work for you. You may need to change the "CandidateID" to match the field you use to identify the candidates.

=DSum("costamount","expenses","OurExpense = true AND CandidateID=" & [CandidateID])

Ken