Getting a balance on a report

[yhgtbfk]

Hi

I am creating rent software and I would like to do a running balance in a report.

In the table there is a field called amountPlus and amountMinus

Record one might have $10 in amountPlus making the balance $10
Record two might have $30 in amountPlus making the balance $40
Record three might have $20 in amountMinus making the balance $20

Having both fields is critical as they are displayed in the DR and CR columns respectively on the report.

(yes I know I could have called the fields DR and CR but I am financially stupid).

The report is displayed in date order, but the transactions are not necessarily entered in date order.

How can I do a running balance on a report?

It needs to take the previous balance, and either add or subtract the value depending on if the value is sitting in the DR column or CR column.

Please give ideas.

Thank you in advance

 

[Pat Hartman]

Add a calculated field to your query:

Select .... Nz(AmountPlus, 0) - Nz(AmountMinus, 0) As NetAmt
From YourTable;

Then create a control bound to NetAmt. Set its RunningSum property to OverAll or if you have groups and you want to sum within the group, to OverGroup.

 

[yhgtbfk]

Thanks Pat