totalling subform records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings. I have a subform that the users (finance dept) would like to have
act like a check register. It does resemble one to fit their needs. What I
did was in the footer of the subform is a field that shows the overall
balance. But, they would like to see a running balance following each each
record, like a check register would show. I can get the balance of each
record but not a running balance.

Is there any way of completing what they are requesting? If so, I will need
guidance on how to do it.

Thanks in advance to anyone responding.
*** John
 
In a report, you can use the RunningSum property of the text box to show the
running balance very simply.

There is much more to the issue in a form, where users can re-order the
records (so they are no longer in date order), or filter them (so not all
records are shown.)

If you want to avoid those issues, and display the balance after the
transaction, based on the dates, regardless of how the records are sorted or
filtered, use a DSum() expression.

The expression will be something like this:
=DSum("Amount", "TransactionTable", "(AccountID = " & Nz([AccountID,0) & ")
AND (TransactionDate <= " & Format(Nz([TransactionDateTime], Now()),
"\#mm\/dd\/yyyy h\:nn\:ss\#")

That assumes AccountID is a Number field, and TransactionDateTime contains
both the date and time so you can distinguish between multiple transactions
on the same day.
 
Back
Top