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.
