Expression

[Viet Ngo]

Hello,

I am using "Form"->"DataSheet View" for data entry.
Expression was created on the form ($balance=$[Deposit]-
[$spend]). Result came out as below:

$Deposit $Spend $Balance
$10.00 $5.00 $5.00
$0.00 $3.00 ($3.00)
$10.00 $2.00 $8.00

Problem:
-Line#2 should show $2 balanced.
-Line#3 should show $10 balanced.

Would someone please show me how to make this work.

Thanks a bunch.
Viet Ngo

 

[Dirk Goldgar]

Hello,

I am using "Form"->"DataSheet View" for data entry.
Expression was created on the form ($balance=$[Deposit]-
[$spend]). Result came out as below:

$Deposit $Spend $Balance
$10.00 $5.00 $5.00
$0.00 $3.00 ($3.00)
$10.00 $2.00 $8.00

Problem:
-Line#2 should show $2 balanced.
-Line#3 should show $10 balanced.

Would someone please show me how to make this work.

Thanks a bunch.
Viet Ngo

You want a running balance, right? Your calculated control is based
only on the current record. You can make a running balance work, but
only if there's a unique key field that can be used to sequence the
transactions in the table. Normally, in the case of a "checkbook" table
like the one you posted, there's a date/time field you can use for the
purpose. Suppose your table is called "tblCheckbook" and it has these
fields:

Table: tblCheckbook
Field: WhenPosted (date/time, primary key)
Field: Deposit (currency)
Field: Withdrawal (currency)

You could create a query like the following, and base your form on it:

------ start of query SQL ------
SELECT
tblCheckbook.WhenPosted,
tblCheckbook.Deposit,
tblCheckbook.Withdrawal,
CCur(
DSum(
"Deposit",
"tblCheckbook",
"WhenPosted<=#" & [WhenPosted] & "#")
-DSum(
"Withdrawal",
"tblCheckbook",
"WhenPosted<=#" & [WhenPosted] & "#")
) AS Balance
FROM tblCheckbook;
------ end of query SQL ------

If your form is based on this query, and the Balance text box on the
form is bound to the calculated Balance field of the query, it should
give you a running balance while permitting data entry.

 

[Guest]

Dirk,

Thank you so much.

I will try with your instruction.

Thanks again,

Viet Ngo.

-----Original Message-----

Hello,

I am using "Form"->"DataSheet View" for data entry.
Expression was created on the form ($balance=$[Deposit]-
[$spend]). Result came out as below:

$Deposit $Spend $Balance
$10.00 $5.00 $5.00
$0.00 $3.00 ($3.00)
$10.00 $2.00 $8.00

Problem:
-Line#2 should show $2 balanced.
-Line#3 should show $10 balanced.

Would someone please show me how to make this work.

Thanks a bunch.
Viet Ngo

You want a running balance, right? Your calculated control is based
only on the current record. You can make a running balance work, but
only if there's a unique key field that can be used to sequence the
transactions in the table. Normally, in the case of a "checkbook" table
like the one you posted, there's a date/time field you can use for the
purpose. Suppose your table is called "tblCheckbook" and it has these
fields:

Table: tblCheckbook
Field: WhenPosted (date/time, primary key)
Field: Deposit (currency)
Field: Withdrawal (currency)

You could create a query like the following, and base your form on it:

------ start of query SQL ------
SELECT
tblCheckbook.WhenPosted,
tblCheckbook.Deposit,
tblCheckbook.Withdrawal,
CCur(
DSum(
"Deposit",
"tblCheckbook",
"WhenPosted<=#" & [WhenPosted] & "#")
-DSum(
"Withdrawal",
"tblCheckbook",
"WhenPosted<=#" & [WhenPosted] & "#")
) AS Balance
FROM tblCheckbook;
------ end of query SQL ------

If your form is based on this query, and the Balance text box on the
form is bound to the calculated Balance field of the query, it should
give you a running balance while permitting data entry.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.