Using a Yes/No field to trigger a calculation in another field - J
in [Queries]
|
Prev: Refering to lowest record in select subquery
Next: 回覆: How could I SELECT the max of 2 different fields?
From: JohnW on 30 Mar 2010 22:20 > 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. > > MultiClassTrigger is a field in my main table that works from a Yes/No combo > box. This will also be in the billing query. 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. I have tried the following IIF statement as an expression in the MultiClassDisc field in the billing query but 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. MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0) I have also tried to put this IIF statement in the AfterUpdate event in the MultiClassDisc field on the form that comes from the billing query. I am having no luck. Any suggestions anyone? Thanks -- JCW
From: Danny on 31 Mar 2010 00:26
In the AfterUpdate event for the MultiClassTrigger field, try this: If MultiClassTrigger = -1 then MultiClassDisc = TotalTuitions*.1 Else MultiClassDisc = 0 End If "JohnW" wrote: > > > 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. > > > > MultiClassTrigger is a field in my main table that works from a Yes/No combo > > box. This will also be in the billing query. > > 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. > > I have tried the following IIF statement as an expression in the > MultiClassDisc field in the billing query but 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. > > MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0) > > I have also tried to put this IIF statement in the AfterUpdate event in the > MultiClassDisc field on the form that comes from the billing query. > > I am having no luck. Any suggestions anyone? Thanks > > > -- > JCW |