In reply:
So what is the Balance table designed to do?
Keep track of in and out out, which an be DIrect Debits (DDRs) or adhoc (inserted into the Balance Sheet manually)
Do you enter an amount in there and reduce as money goes out?
Yes
Or does it reflect what is in the DDR? No DDR is just an outgoing (it uses TYpe (+ or -) to calculate whether it should add or subtract the payeeamt
OK, I need a reset here, back to your original post.
Would it make sense to have the Balance table actually have the balance in it.
ie use VBA to take the value in the DDR table and add/subtract it from the Current balance in the Balance table with the action date in both tables so you know which DDRs etc have been actioned.
Also have a Deposit Table that does the same, adds it to the current balance in the balance table.
This will give you a simple query that you can filter.
I don't know how you get the DDRs in to the DDR table are they imported?
Anyway, here is the database with the Public Variable and the query with module, which you may find useful in the future.
The module is in the column and form field expr1.