Showing Balances in MS Access Reports

  • Thread starter Thread starter yk
  • Start date Start date
Y

yk

I hope someone could help me out with my problem.

I would like to print reports with balances outstanding, exactly the same as
we find in our bank's savings book. For example, if I have a balance of USD
1,000.00 in the bank on 1st June 2004 and subsequently on 3rd June 2004 I
withdrew USD 455.00, and then on 7th June 2004, I deposited USD 1000.00.

How could I show that on 1st June I have USD 1,000.00, on 3rd June USD545.00
and on 7th June USD1,545.00 ? And also the total at the end of June 2004?

I will appreciate very much your help.

Thank You.
 
The general idea here is to use a second control for the amount field, and
set its Running Sum property to Over All, but the exact way to do it depends
on your report design.

If your individual movements are all displayed in a single textbox on the
report and are properly signed (with a - for withdrawals), then it's the
easiest case, just do as above and you're done.

If you are using one control for credits and another for debits, so you get
the two in separate columns on the report, you will need a textbox with its
controlsource set to:

=[txtCredit]-[txtDebit]

and its running sum prop. as above. I have assumed that the two controls for
debit and cretid are named as above, change as required.

The toughest case is if you use one control for both and no signs, in which
case you will need a calculated control, which looks up some other control,
like a movement description, to establish if it's a debit or credit, and
apply the sign on the amount. It would probably be something like:
=IIf([Description]="Deposit", 1,-1) * [txtAmount]
and, again with its running sum property to Over All.

HTH,
Nikos
 
Back
Top