Summing calculated text boxes

[perrymans]

I have two textboxes as follows:

txtDuesYearFee
ControlSource:

=IIf(Nz([cboDuesYearID],"")="","",DLookUp("[DuesYearFee]","Tbl_DuesYearFee","[DuesYearID] = " & [cboDuesYearID]))

AND

txtBalance
ControlSource:

=IIf(NZ([txtAmountPaid],"")="","",[txtAmountPaid]-[txtDuesYearFee])

So the first is looking up the DuesYearFee, and the second is calculating balance based on the DuesYearFee and the AmountPaid by the member.

This is a continuous form.

I would like to place a textbox in the Form Footer to Sum([txtBalance]), but it does not work.

My guess is because it is trying to calculate calculated information (maybe happening at the same time).

How would this be done?

Thanks. Sean.

 

[AlexMidd]

You might need to create a field in your query for the form, which calculates the same result as txtBalance, then set the control source for the textbox in the form footer to that field.

Have fun! )

Alex Middleton

 

[PHV]

Perhaps this ?
=Sum(IIf(NZ([txtAmountPaid],0)=0,0,[txtAmountPaid]-[txtDuesYearFee]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[perrymans]

No dice. #Error

Can't do it in the query because I won't be able to add records because of the table structure.

I may just pass if too difficult.

Thanks.

Sean.

 

[PHV]

And this ?
=Sum(IIf(Val([txtAmountPaid] & "")=0, 0, Val([txtAmountPaid] & "")-Val([txtDuesYearFee] & "")))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886