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,

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CLM" <(E-Mail Removed)> wrote in message
news:35A1FC47-3282-4D16-A0E5-(E-Mail Removed)...
>
> 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

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" wrote:

> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "CLM" <(E-Mail Removed)> wrote in message
> news:35A1FC47-3282-4D16-A0E5-(E-Mail Removed)...
> >
> > 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
>
>
>