Opening balance worked into running balance.

[Perfac]

When I create a running balance on a report. How do I bring the opening balance into the calculation? I.e.
Debtor account Statement from 1Dec17 to 31 Dec17. Opening balance was derived through complicated calculated field in a query.

Running Balance(should be)
Opening balance 30 Nov 1000.00


Debit in Dec Invoice 700.00 1700.00
Credit in Dec Payment 500.00 1200.00

Closing balance 31Dec17 therefor 1200.00

Thank you.

[ranman256]

the starting balance would be in the query.
the running balance is in the report where the field property: RUNNING SUM = TRUE

[Perfac]

Thank you. As you may see. On my report there is an opening balance of -2894.49, it should be calculated into the running balance first line which shows -3376.13. The -3376.13 should be -6270.76. Attachment 31944

[Minty]

Add the opening balance to each detail line total.
Then you will get the correct running balance.

[June7]

Appears Balance textbox is set for RunningSum. As Minty says, include reference to the Opening Balance textbox/field to add it into the Balance calculation.

Show the expression in the Balance textbox if you need more help.

[Perfac]

I have a union query with field named "balance" which in the report I applied the running balance option. I tried what you suggested. In the query I created a field "balance2" and the expression is now =Forms![f05PrtLdgAcc]![T08]+balance
. Problem is the running balance adds the opening balance each time and the opening balance already included in the balance above, so it snowballs. I haven’t spent enough minutes trying to find a way to have it in only once. If you understand. Please help.

[Minty]

Add it after the Running sum has done it's bit.

[June7]

Sorry, my suggestion was a bit of misdirection.

Have a hidden textbox for the Running Sum.

Have another textbox with expression that adds the Running Sum textbox with the opening balance textbox.

Is the Balance field the difference between Debit and Credit fields?

[Perfac]

Thanks Minty. I don't understand yet. Add after?

[Perfac]

Thanks June7. Yes the balance field is the difference between debit and credit fields. Still not sure how to include the opening balance in the running sum.

[Minty]

In the report you would make it do this;

	Opening Balance	-1234
Debit	Credit	Running Balance	Hidden O/B	Actual Balance
0	3432	-3432	-1234	-4666
0	5431	-8863	-1234	-10097
0	2134	-10997	-1234	-12231
0	1256	-12253	-1234	-13487
0	324	-12577	-1234	-13811
0	1903	-14480	-1234	-15714

Does that make sense?

[June7]

My suggestion is to have a hidden textbox with the running sum, call it txtBal. The opening balance textbox is named txtOpen. Then another textbox with calc: =[txtBal] + [txtOpen].

The hidden control for the opening balance suggested by Minty is not needed.