Check box calculation/value

J

Jannie

I have 2 check boxes. I want to add them together as a value if they are
checked but do it in a IIF clause. Example. Flex Plan and Cafe Plan. If
you participate in the Flex plan the box is checked. If you participate in
the Cafe Plan the box is checked. The value for the the Flex plan is $1.00,
the value for the Cafe plan is $5.00. How can I EASILY say if you check
either box it will add 1.00 and 5.00 or just 1.00 or 5.00 or both.
Thanks.
Jannie
 
B

Brendan Reynolds

Here's an example of how you might do this in a query ...

Fee: 1*Abs([FlexPlan])+5*Abs([CafePlan])

True (a checked box) is -1, while False (unchecked) is 0, and the Abs()
function returns the absolute value of a number - Abs(1) and Abs(-1) both
return 1. So the result of the above expression is that 1 gets multiplied by
1 if FlexPlan is checked or 0 if it isn't, while 5 gets multiplied by 1 if
CafePlan is checked or 0 if it isn't.
 
J

John Vinson

I have 2 check boxes. I want to add them together as a value if they are
checked but do it in a IIF clause. Example. Flex Plan and Cafe Plan. If
you participate in the Flex plan the box is checked. If you participate in
the Cafe Plan the box is checked. The value for the the Flex plan is $1.00,
the value for the Cafe plan is $5.00. How can I EASILY say if you check
either box it will add 1.00 and 5.00 or just 1.00 or 5.00 or both.
Thanks.
Jannie
Click to expand...

You may want to store the prices for the plans in a little two-row
table so they can be changed more easily in the future; if the $5.00
is embedded in code on a form or a query it may be hard to find!

If you want to do so, you could set the Control Source of a textbox
to:

= IIf([chkFlex], DLookUp("Cost", "PlanCosts", "Plan = 'Flex'), 0) +
IIf([chkCafe], DLookUp("Cost", "PlanCosts", "Plan = 'Cafe'), 0)

If you want to risk storing data, it's a bit simpler:

= IIF([chkFlex], 1.00, 0) + IIF([chkCafe], 5.00, 0)

John W. Vinson[MVP]
 
Top