Account Balance Formula

[JamesJ]

Hi. I need a formula that adds the value from, say, a Deposit field and
subtracts
from a Debit field like a checking account register and keep a running total
in either a continuous form or datasheet view. Also, per record, there
should
only be a value other than $0.00 in one or the other (Deposit, Debit) but
not
both.
Does this 'Balance' field need to be bound or unbound?

Any help will be appreciated.
James

 

[Allen Browne]

To get the balance, we have to define what is meant by summing the "earlier"
records. I will assume you have an AutoNumber field named ID, and you
"earlier" means records with a lower ID value.

Solution 1: report
In a report, just use the Running Sum property of a text box to collect a
running sum of both Deposit and Debit. Hide these text boxes if you want,
and display the text box that shows the difference.

Solution 2: form
In a form, you could set the Control Sorce of a text box to:
=DSum("Nz([Deposit],0) - Nz([Debit],0)", "Table1", "ID <= " &
Nz([ID],0))
This will be slow to calculate.

Solution 3: query
In a query, if you do not mind a read-only result, it would be more
efficient to use a subquery. Type an expression such as this into a fresh
column in the Field row:
Balance: CCur(Nz((SELECT Sum(Nz([Deposit],0) - Nz([Debit],0))
FROM Table1 AS Dupe WHERE Dupe.ID <= Table1.ID),0))
This assumes your main query already has Table1 in it.

Under no circumstances should you consider adding a Balance field to your
table.

 

[JamesJ]

One problem. I'm using the expression in Control Source of a text
box. I also have a date field which I want to order the records by.
The expression calculates the Balance by the TransactionID field.
So, the Balance doesn't calculate by the records ordered by the
date field. Hope I explained that properly.
Is it possible to use the query without being read only??

James

To get the balance, we have to define what is meant by summing the
"earlier" records. I will assume you have an AutoNumber field named ID,
and you "earlier" means records with a lower ID value.

Solution 1: report
In a report, just use the Running Sum property of a text box to collect a
running sum of both Deposit and Debit. Hide these text boxes if you want,
and display the text box that shows the difference.

Solution 2: form
In a form, you could set the Control Sorce of a text box to:
=DSum("Nz([Deposit],0) - Nz([Debit],0)", "Table1", "ID <= " &
Nz([ID],0))
This will be slow to calculate.

Solution 3: query
In a query, if you do not mind a read-only result, it would be more
efficient to use a subquery. Type an expression such as this into a fresh
column in the Field row:
Balance: CCur(Nz((SELECT Sum(Nz([Deposit],0) - Nz([Debit],0))
FROM Table1 AS Dupe WHERE Dupe.ID <= Table1.ID),0))
This assumes your main query already has Table1 in it.

Under no circumstances should you consider adding a Balance field to your
table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi. I need a formula that adds the value from, say, a Deposit field and
subtracts
from a Debit field like a checking account register and keep a running
total
in either a continuous form or datasheet view. Also, per record, there
should
only be a value other than $0.00 in one or the other (Deposit, Debit) but
not
both.
Does this 'Balance' field need to be bound or unbound?

Any help will be appreciated.
James

 

[Allen Browne]

DSum() can work with the 3rd argument based on a date:
=DSum("Nz([Deposit],0) - Nz([Debit],0)", "Table1", "[TransactionDate] <=
" &
Format(Nz([TransactionDate], #1/1/1900#), "\#mm\/dd\/yyyy\#"))
That will give unacceptable answers if there are 2 transactions on the same
date.

You can use DSum() in a query if you want it updateable. Very slow.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

One problem. I'm using the expression in Control Source of a text
box. I also have a date field which I want to order the records by.
The expression calculates the Balance by the TransactionID field.
So, the Balance doesn't calculate by the records ordered by the
date field. Hope I explained that properly.
Is it possible to use the query without being read only??

James

To get the balance, we have to define what is meant by summing the
"earlier" records. I will assume you have an AutoNumber field named ID,
and you "earlier" means records with a lower ID value.

Solution 1: report
In a report, just use the Running Sum property of a text box to collect a
running sum of both Deposit and Debit. Hide these text boxes if you want,
and display the text box that shows the difference.

Solution 2: form
In a form, you could set the Control Sorce of a text box to:
=DSum("Nz([Deposit],0) - Nz([Debit],0)", "Table1", "ID <= " &
Nz([ID],0))
This will be slow to calculate.

Solution 3: query
In a query, if you do not mind a read-only result, it would be more
efficient to use a subquery. Type an expression such as this into a fresh
column in the Field row:
Balance: CCur(Nz((SELECT Sum(Nz([Deposit],0) - Nz([Debit],0))
FROM Table1 AS Dupe WHERE Dupe.ID <= Table1.ID),0))
This assumes your main query already has Table1 in it.

Under no circumstances should you consider adding a Balance field to your
table.

Hi. I need a formula that adds the value from, say, a Deposit field and
subtracts
from a Debit field like a checking account register and keep a running
total
in either a continuous form or datasheet view. Also, per record, there
should
only be a value other than $0.00 in one or the other (Deposit, Debit)
but not
both.
Does this 'Balance' field need to be bound or unbound?