# Calculating a new balance for each line item reported

=?Utf-8?B?Q0xN?=
Guest
Posts: n/a

 22nd Sep 2004
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,

Allen Browne
Guest
Posts: n/a

 22nd Sep 2004
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

=?Utf-8?B?Q0xN?=
Guest
Posts: n/a

 22nd Sep 2004
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

>
>
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post =?Utf-8?B?UGV0ZXIgUi4=?= Microsoft Excel Worksheet Functions 1 24th Aug 2007 03:04 AM Chris Cherry Spyware Discussion 2 15th Jan 2005 06:01 PM =?Utf-8?B?anNjY29ycHM=?= Microsoft Access Queries 1 6th Dec 2004 06:31 PM Michelle Microsoft Access Form Coding 5 10th May 2004 11:21 PM Matt Darcy Microsoft Windows 2000 0 24th Sep 2003 05:27 PM

Features