insert the previous balance into new record(arrears)

[sahh1419]

Dear i am using this query to insert the previouse balance into arrears as shown in the figure. it will insert some record correct and some are not correct.pls help me.

SELECT fees.[gr no], sum((Admission+Registration+Fees.Tuition+[Fine]+Fees.Transport+[Exam]+[Misc])-Fees.Paid) AS bala
FROM fees
WHERE fees.balance<=0 and [gr no]=[gr no]
GROUP BY [gr no];

please see the attached file.

 

[CJ_London]

To show per your image, you would have to calculate the arrears and apply it to the default value, so it cannot be done in the form recordsource query.

An alternative in vba form current event is to use the following:

Arrears.Default=dsum("[Admission]+[Registration]+[Tuition]+[Fine]+[Transport]+[Exam]+[Misc]-[Paid]","Fees","[GR No]=" & me.[GR No])
If Arrears.Default>0 then Arrears.Default=0

 

[missinglinq]

Note that this problem has been triple-posted, also being located at

http://www.accessforums.net/forms/insert-previous-balance-into-new-record-arrears-40481.html

http://www.utteraccess.com/forum/Put-Balance-Arrears-t2013247.html

Linq ;0)>