Calculating a new balance for each line item reported

C

CLM

Hello,

I have Excel spreadsheet that does the following:
deposit = 10,000.00
date payment Avail Bal
1/1/04 500.00 9,500
2/1/04 500.00 9,000
3/1/04 250.00 8,750
4/1/04 1000.00 7,750

Basically how do I do that in a report where we do NOT have the Avail Bal
stored. It has to be calculated either in the report query or in the report
sql code. Any ideas?

Thx,
 
A

Allen Browne

Use Running Sum.

This example assumes you have a text box named txtOpenBal in the report
header that has the starting balance (original deposit?).

1. Add a text box to the Detail section, and set these properties:
ControlSource =[payment]
Running Sum Over Group
Visible No
Name txtPaymentRS

2. Add another text box so show the balance. Set these properties:
ControlSource =[txtOpenBal] - [txtPaymentRS]
Format Currency
 
C

CLM

Hi Allen,

I actually don't have a one in the header but that can be easily
accomplished. Thx for the quick reply I will test it out tomorrow.

Clm

Allen Browne said:
Use Running Sum.

This example assumes you have a text box named txtOpenBal in the report
header that has the starting balance (original deposit?).

1. Add a text box to the Detail section, and set these properties:
ControlSource =[payment]
Running Sum Over Group
Visible No
Name txtPaymentRS

2. Add another text box so show the balance. Set these properties:
ControlSource =[txtOpenBal] - [txtPaymentRS]
Format Currency

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

CLM said:
I have Excel spreadsheet that does the following:
deposit = 10,000.00
date payment Avail Bal
1/1/04 500.00 9,500
2/1/04 500.00 9,000
3/1/04 250.00 8,750
4/1/04 1000.00 7,750

Basically how do I do that in a report where we do NOT have the Avail Bal
stored. It has to be calculated either in the report query or in the
report
sql code. Any ideas?

Thx
Click to expand...
Click to expand...
 
Top