Running Balance in forms or queries

[Windward]

I have been trying to produce a report which lists items purchased from accounts with several suppliers and shows payments into these accounts and a running/outstanding balance.

My access experience is poor and I found a template/database on the Microsoft site which looked exactly as I wanted, but it too does not seem to show the calculated balance anywhere. (http://office.microsoft.com/en-us/templates/results.aspx?qu=access%20databases&av=zac110#ai:TC 001017534|)



It has a calculated text box on two of the reports but it does not seem to work other than as a sum in the footer. Can anyone suggest where am I going wrong (in simple terms for a bear of little brain).

[June7]

That link does not go to template download. What template did you use?

[Windward]

That link does not go to template download. What template did you use?

The link takes you to a microsoft download for an Acounting Ledger database which is ideal for what I am trying to achieve .......if it worked. I have downloaded it and the balance column in the reports doesn't work. This is exactly the problem I have with my design. The report uses text boxes with formula in the same way as mine.

[June7]

Very weird link. It takes me to a 'No results returned for access databases' page on the MS site. This time I had that page do a search and comes back with an assortment of databases. One is called 'Accounting ledger database' so I assume that is the one.

Which two reports do you want to modify?

Textboxes on reports have a RunningSum property. I have never used it but that might be what you need.

[ssanfu]

Very weird link. It takes me to a 'No results returned for access databases' page on the MS site. This time I had that page do a search and comes back with an assortment of databases. One is called 'Accounting ledger database'. Is that the one you are using?

June,
The link worked for me. I downloaded and zipped the mdb. It is in A2K3 format; I have A2K

Steve

[Windward]

Very weird link. It takes me to a 'No results returned for access databases' page on the MS site. This time I had that page do a search and comes back with an assortment of databases. One is called 'Accounting ledger database' so I assume that is the one.

Which two reports do you want to modify?

Textboxes on reports have a RunningSum property. I have never used it but that might be what you need.

This is the correct database. All the reports show a balance heading and what I was hoping to achieve was a running balance that adds new invoices to the total and deducts payments made in. A running sum text box in a report won't do this as it just keeps adding to the total.

[June7]

Part of the problem is null fields. Normal math calcs with null return null. Need to handle null.

Try this on the Account Summary report:

1. expression in the Balance textbox: =Nz([DepositAmount],0)-Nz([WithdrawalAmount],0)

2. set Balance textbox RunningSum property to Yes

3. expression in the Balance sum textbox: =Sum(Nz([DepositAmount],0)-Nz([WithdrawalAmount],0))

Is that getting close to what you want?

[Windward]

Many thanks June7

Exactly what I needed.