How do I create a running account balance in an Access form?

T

tina

if you want to show the "running" balance on each record, and assuming that
you have a Date/Time field that gives you a chronological order for the
records in the table: you can use an expression in an unbound control on
the form to sum the balance for every record * equal and prior to* the
current record. something along the lines of

=DSum("[ValueField]", "TableName", "[DateField] <= #" & [DateField] & "#")

substitute the correct table and field names, of course.

hth
 
C

Cory

This helps but I am not quite there yet. I have a table with transactions to
various account numbers. I have a form that shows account number and some
account infromation, then a subform which displays transactions to that
account. When I create the field and enter the expression like you show
below, I get the running balance of ALL transactions before that date, not
just the ones for that account. What have I done wrong?? Thanks for your
help.

tina said:
if you want to show the "running" balance on each record, and assuming that
you have a Date/Time field that gives you a chronological order for the
records in the table: you can use an expression in an unbound control on
the form to sum the balance for every record * equal and prior to* the
current record. something along the lines of

=DSum("[ValueField]", "TableName", "[DateField] <= #" & [DateField] & "#")

substitute the correct table and field names, of course.

hth
Click to expand...
 
T

tina

nothing wrong. we just need to include criteria to specify the account.
presumably there is a field in the transactions table which links to the
accounts table, and identifies each account. i'll just call that field
AccountID. change the expression to

=DSum("[ValueField]", "TableName", "[AccountID] = " & [AccountID] & " And
[DateField] <= #" & [DateField] & "#")

if the account ID field is Text data type, rather than Number data type,
then the syntax would be

=DSum("[ValueField]", "TableName", "[AccountID] = '" & [AccountID] & "' And
[DateField] <= #" & [DateField] & "#")

again, you need to substitute the correct name of the account ID field.

hth


Cory said:
This helps but I am not quite there yet. I have a table with transactions to
various account numbers. I have a form that shows account number and some
account infromation, then a subform which displays transactions to that
account. When I create the field and enter the expression like you show
below, I get the running balance of ALL transactions before that date, not
just the ones for that account. What have I done wrong?? Thanks for your
help.

tina said:
if you want to show the "running" balance on each record, and assuming that
you have a Date/Time field that gives you a chronological order for the
records in the table: you can use an expression in an unbound control on
the form to sum the balance for every record * equal and prior to* the
current record. something along the lines of

=DSum("[ValueField]", "TableName", "[DateField] <= #" & [DateField] & "#")

substitute the correct table and field names, of course.

hth
Click to expand...
Click to expand...
 
C

Cory

Works like a charm....thanks for all of your help.

tina said:
nothing wrong. we just need to include criteria to specify the account.
presumably there is a field in the transactions table which links to the
accounts table, and identifies each account. i'll just call that field
AccountID. change the expression to

=DSum("[ValueField]", "TableName", "[AccountID] = " & [AccountID] & " And
[DateField] <= #" & [DateField] & "#")

if the account ID field is Text data type, rather than Number data type,
then the syntax would be

=DSum("[ValueField]", "TableName", "[AccountID] = '" & [AccountID] & "' And
[DateField] <= #" & [DateField] & "#")

again, you need to substitute the correct name of the account ID field.

hth


Cory said:
This helps but I am not quite there yet. I have a table with transactions to
various account numbers. I have a form that shows account number and some
account infromation, then a subform which displays transactions to that
account. When I create the field and enter the expression like you show
below, I get the running balance of ALL transactions before that date, not
just the ones for that account. What have I done wrong?? Thanks for your
help.

tina said:
if you want to show the "running" balance on each record, and assuming that
you have a Date/Time field that gives you a chronological order for the
records in the table: you can use an expression in an unbound control on
the form to sum the balance for every record * equal and prior to* the
current record. something along the lines of

=DSum("[ValueField]", "TableName", "[DateField] <= #" & [DateField] & "#")

substitute the correct table and field names, of course.

hth
Click to expand...
Click to expand...
Click to expand...
 
T

tina

you're welcome :)


Cory said:
Works like a charm....thanks for all of your help.

tina said:
nothing wrong. we just need to include criteria to specify the account.
presumably there is a field in the transactions table which links to the
accounts table, and identifies each account. i'll just call that field
AccountID. change the expression to

=DSum("[ValueField]", "TableName", "[AccountID] = " & [AccountID] & " And
[DateField] <= #" & [DateField] & "#")

if the account ID field is Text data type, rather than Number data type,
then the syntax would be

=DSum("[ValueField]", "TableName", "[AccountID] = '" & [AccountID] & "' And
[DateField] <= #" & [DateField] & "#")

again, you need to substitute the correct name of the account ID field.

hth


Cory said:
This helps but I am not quite there yet. I have a table with
Click to expand...
transactions
to
various account numbers. I have a form that shows account number and some
account infromation, then a subform which displays transactions to that
account. When I create the field and enter the expression like you show
below, I get the running balance of ALL transactions before that date, not
just the ones for that account. What have I done wrong?? Thanks for your
help.

:

if you want to show the "running" balance on each record, and
Click to expand...
assuming
that
you have a Date/Time field that gives you a chronological order for the
records in the table: you can use an expression in an unbound
Click to expand...
control
on
the form to sum the balance for every record * equal and prior to* the
current record. something along the lines of

=DSum("[ValueField]", "TableName", "[DateField] <= #" & [DateField]
Click to expand...
&
"#")
substitute the correct table and field names, of course.

hth
Click to expand...
Click to expand...
Click to expand...
 
N

Nadine

Hi Tina,
I am experiencing the same problem as Cory only that in my case the balance
of the previous date where the selected account was modified enter in
consideration. My question is how to express in Ms Access Current Balance
=Previous balance + Debits -Credit. Where previous balance is the balance of
the latest day the account was modified.
In my table I have the following fields:
Transaction_ID
Account#
Date
Credit
Debit
Balance.
Please I need your help.
Nadine


tina said:
nothing wrong. we just need to include criteria to specify the account.
presumably there is a field in the transactions table which links to the
accounts table, and identifies each account. i'll just call that field
AccountID. change the expression to

=DSum("[ValueField]", "TableName", "[AccountID] = " & [AccountID] & " And
[DateField] <= #" & [DateField] & "#")

if the account ID field is Text data type, rather than Number data type,
then the syntax would be

=DSum("[ValueField]", "TableName", "[AccountID] = '" & [AccountID] & "' And
[DateField] <= #" & [DateField] & "#")

again, you need to substitute the correct name of the account ID field.

hth


Cory said:
This helps but I am not quite there yet. I have a table with transactions to
various account numbers. I have a form that shows account number and some
account infromation, then a subform which displays transactions to that
account. When I create the field and enter the expression like you show
below, I get the running balance of ALL transactions before that date, not
just the ones for that account. What have I done wrong?? Thanks for your
help.

tina said:
if you want to show the "running" balance on each record, and assuming that
you have a Date/Time field that gives you a chronological order for the
records in the table: you can use an expression in an unbound control on
the form to sum the balance for every record * equal and prior to* the
current record. something along the lines of

=DSum("[ValueField]", "TableName", "[DateField] <= #" & [DateField] & "#")

substitute the correct table and field names, of course.

hth
Click to expand...
Click to expand...
Click to expand...
 
Top