Using a Yes/No field to trigger a calculation in another field - J

J

JohnW

I am trying to use an IIF statement to have a filed calculate based on
another field having either Yes or No in it. The fields are:

TotalTuitions - all the tuitions added together
MultiClassTrigger - Yes/No fiield
MultiClassDisc - 10% discount to be calculated depending on Yes/No

IIF statement -
MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0)

This will work the first time that Yes is selected in the Trigger field but
then the value will stay when I change the Trigger field back to No. With No
selected it should change the discount field back to zero.

I know I am missing something but can't come up with anything that will
work. Any suggestions would be appreciated. Thanks.
 
K

kc-mass

Hi John,

Put your code in the afterupdate event of Multiclass trigger.

Every time you update thr MultiClassTrigger the MultiClassDiscount will
reset.

Regards

kevin
 
J

JohnW

Kevin

When I put in my IIF statement into the afterupdate of the MultiClassTrigger
field it gives me a "The expression you entered contains invalid syntax" "You
may have entered an operand without an operator.

I am using

MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0)

Suggestions? Thanks

--
JCW


kc-mass said:
Hi John,

Put your code in the afterupdate event of Multiclass trigger.

Every time you update thr MultiClassTrigger the MultiClassDiscount will
reset.

Regards

kevin


JohnW said:
I am trying to use an IIF statement to have a filed calculate based on
another field having either Yes or No in it. The fields are:

TotalTuitions - all the tuitions added together
MultiClassTrigger - Yes/No fiield
MultiClassDisc - 10% discount to be calculated depending on Yes/No

IIF statement -
MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0)

This will work the first time that Yes is selected in the Trigger field
but
then the value will stay when I change the Trigger field back to No. With
No
selected it should change the discount field back to zero.

I know I am missing something but can't come up with anything that will
work. Any suggestions would be appreciated. Thanks.


.
 
J

John Spencer

If you are doing this in VBA, you need to put

IIf([MultiClassTrigger]=True,[TotalTuitions]*0.1,0)

into the VBA code. You should have
[Expression]
in the After Update property and then click the three dots at the end to open
the VBA window and put the above statement in the code.

I would do this slightly different. I would set the control source of
TotalTuitions to the expression.
=IIf([MultiClassTrigger]=True,[TotalTuitions]*0.1,0)

And it might be necessary for you to force a recalc of the totalTuitions
control when you change the value of the MultiClassTrigger control. If the
total tuitions control fails to update try putting the following in the after
update event code of the MultiClassTrigger control.
Me.TotalTuitions.Requery


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
J

JohnW

John
I am not sure if I am following. I am tryin to use the MultiClassTrigger
field to force the calculation of the MultiClassDisc field. When the Trigger
field is clicked to Yes I need it to take TotalTuitions*.1 to calculate the
10% discount. When it is clicked No I want MultiClassDisc to equal zero.

Maybe this is what you're trying to do but when I put the code in the after
update field nothing is happening.

I apprecaite your help and patience.
--
JCW


John Spencer said:
If you are doing this in VBA, you need to put

IIf([MultiClassTrigger]=True,[TotalTuitions]*0.1,0)

into the VBA code. You should have
[Expression]
in the After Update property and then click the three dots at the end to open
the VBA window and put the above statement in the code.

I would do this slightly different. I would set the control source of
TotalTuitions to the expression.
=IIf([MultiClassTrigger]=True,[TotalTuitions]*0.1,0)

And it might be necessary for you to force a recalc of the totalTuitions
control when you change the value of the MultiClassTrigger control. If the
total tuitions control fails to update try putting the following in the after
update event code of the MultiClassTrigger control.
Me.TotalTuitions.Requery


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Kevin

When I put in my IIF statement into the afterupdate of the MultiClassTrigger
field it gives me a "The expression you entered contains invalid syntax" "You
may have entered an operand without an operator.

I am using

MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0)

Suggestions? Thanks
.
 
J

John Spencer

Are we talking about FIELDS or CONTROLS?

I would not have a MultiClassDisc field in a table since it is dependent on
other fields. I assume that MultiClassTrigger is a True/False field. Is
TotalTuitions a field in your table or is it a control that is a sum of some
field values?

If TotalTuitions is a control with a source that looks like:
=Sum([CourseTuition])
then we need to do things differently than if TotalTuitions is a FIELD in a table.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
J

John Spencer

Sorry. I will be offline for the next few days. You might start a new thread
and see if someone can help you.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John

As I read back thru my posts I realize I probably wasn't clear enough. I
have a billing form that was created from a query based on the table that all
these fields are from. In this billing query is where all these calculations
are happening so they can be displayed on the billing form.

TotalTuitions is a field that exists in my main table and it is calculated
in the billing query by adding (4) other tuition type fields.

MultiClassDisc is a field in my main table and is in the billing query.
Initially we were not going to calculate this, they were going to just type
it in.

MultiClassTrigger is a field in my main table that works from a Yes/No combo
box. This will also be in the billing query.

The SQL view of the billing query is pretty lengthy but here it is......

SELECT ECG.Gymnasts, ECG.BillingName, ECG.BillingAddress, ECG.luBillingCity,
ECG.BillingState, ECG.luBillingZip, ECG.StartDate, ECG.TodayDate,
DateDiff("d",[StartDate],[TodayDate]) AS Days, ECG.Hours, ECG.Tuition,
Switch([Days]>=365 And [Days]<730,0.05,[Days]>=730 And
[Days]<1095,0.1,[Days]>=1095 And [Days]<1460,0.15,[Days]>=1460 And
[Days]<1825,0.2,[Days]>=1825 And [Days]<2190,0.25,[Days]>=2190 And
[Days]<2555,0.3,[Days]>=2555 And [Days]<2920,0.35,[Days]>=2920 And
[Days]<3285,0.4,[Days]>=3285 And [Days]<3650,0.45,[Days]>=3650 And
[Days]<4015,0.5,[Days]>4015,0.5,True,1) AS CSP, ECG.MultiClassDisc,
ECG.MultiChildDisc, ECG.MilitaryDisc, ECG.MiscDisc,
([TotalTuitions])-[MultiClassDisc]-[MultiChildDisc]-[MilitaryDisc]-[MiscDisc]
AS MonthlyTotal, ECG.[Late Fee], ECG.BalanceOverDue,
[MonthlyTotal]+[BalanceOverDue]+[Late Fee]+[MiscPay] AS BalanceDue,
ECG.Gymnasts2, ECG.Gymnasts3, ECG.Gymnasts3, ECG.StartDate2, ECG.StartDate3,
ECG.StartDate4, ECG.Tuition2, ECG.Tuition3, ECG.Tuition4, Switch([Days2]>=365
And [Days2]<730,0.05,[Days2]>=730 And [Days2]<1095,0.1,[Days2]>=1095 And
[Days2]<1460,0.15,[Days2]>=1460 And [Days2]<1825,0.2,[Days2]>=1825 And
[Days2]<2190,0.25,[Days2]>=2190 And [Days2]<2555,0.3,[Days2]>=2555 And
[Days2]<2920,0.35,[Days2]>=2920 And [Days2]<3285,0.4,[Days2]>=3285 And
[Days2]<3650,0.45,[Days2]>=3650 And [Days2]<4015,0.5,[Days2]>4015,0.5,True,1)
AS CSP2, Switch([Days3]>=365 And [Days3]<730,0.05,[Days3]>=730 And
[Days3]<1095,0.1,[Days3]>=1095 And [Days3]<1460,0.15,[Days3]>=1460 And
[Days3]<1825,0.2,[Days3]>=1825 And [Days3]<2190,0.25,[Days3]>=2190 And
[Days3]<2555,0.3,[Days3]>=2555 And [Days3]<2920,0.35,[Days3]>=2920 And
[Days3]<3285,0.4,[Days3]>=3285 And [Days3]<3650,0.45,[Days3]>=3650 And
[Days3]<4015,0.5,[Days3]>4015,0.5,True,1) AS CSP3, Switch([Days4]>=365 And
[Days4]<730,0.05,[Days4]>=730 And [Days4]<1095,0.1,[Days4]>=1095 And
[Days4]<1460,0.15,[Days4]>=1460 And [Days4]<1825,0.2,[Days4]>=1825 And
[Days4]<2190,0.25,[Days4]>=2190 And [Days4]<2555,0.3,[Days4]>=2555 And
[Days4]<2920,0.35,[Days4]>=2920 And [Days4]<3285,0.4,[Days4]>=3285 And
[Days4]<3650,0.45,[Days4]>=3650 And [Days4]<4015,0.5,[Days4]>4015,0.5,True,1)
AS CSP4, ECG.Hours2, ECG.Hours3, ECG.Hours4, ECG.Gymnasts4,
DateDiff("d",[StartDate2],[TodayDate]) AS Days2,
DateDiff("d",[StartDate3],[TodayDate]) AS Days3,
DateDiff("d",[StartDate4],[TodayDate]) AS Days4, ECG.[Session Payment],
ECG.BillComments, ECG.InvComments, IIf([CSP]<1,[Tuition]*[CSP],0) AS CSPCalc,
IIf([CSP2]<1,[Tuition2]*[CSP2],0) AS CSPCalc2,
IIf([CSP3]<1,[Tuition3]*[CSP3],0) AS CSPCalc3,
IIf([CSP4]<1,[Tuition4]*[CSP4],0) AS CSPCalc4,
IIf([Tuition]>0,[Tuition]-[CSPCalc],0) AS TotTuition,
IIf([Tuition2]>0,[Tuition2]-[CSPCalc2],0) AS TotTuition2,
IIf([Tuition3]>0,[Tuition3]-[CSPCalc3],0) AS TotTuition3,
IIf([Tuition4]>0,[Tuition4]-[CSPCalc4],0) AS TotTuition4, ECG.lupClass,
ECG.MiscPay, ECG.MiscDiscDesc, ECG.StartDateDesc, ECG.MiscPayDesc, ECG.Email,
ECG.luWaiveLateFee, ECG.MultiClassTrigger,
([TotTuition]+[TotTuition2]+[TotTuition3]+[TotTuition4]) AS TotalTuitions
FROM ECG
WHERE (((ECG.Gymnasts)=[Forms]![Gymnasts List]![cboGymnasts]) AND
((ECG.lupActive) Like "*yes*"));


I hope this helps. Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top