Running sum on a subform - DSum???

[Guest]

I have a subform which I want to show grant money received and spent and a
running balance. I have been able to accomplish this on a report using the
CCur function.

On the subform (based on a table Accountability) I have an Amount field in
which I enter the amount either positive or negative. If the Amount is
positive it also shows up in a calculated field labeled Income; if the Amount
is negative it shows up in a calculated field labeled Expense. Both the
Income and Expense field are locked. I have tried using a DSum function in a
calculated Balance field but to date have not been successful.

I have never used this function before so my Syntax may be faulty. I have
tried the following:

=DSum("[Amount]","[Accountability]",)

In the above Amount is the field (positive or negative) Accountability is
the name of the underlying table of the subform. I have not included a third
piece of the argument because I am not looking for a particular category –
e.g. what was spent on stationery.

Can anyone solve my riddle? Any help much appreciated.

Mary

 

[Dale Fye]

Mary,

DSUM is used to compute the sum of values in a column of a table. I'm not
entirely sure that is what you want to do here. If it is, you are on the
right track, but probably need to leave off the last comma in your DSUM
expression. It is only necessary if the optional criteria argument is being
passed.

When you say "field" below are you actually talking about textbox controls
on your form? If so, you can compute the value in your txt_Balance control
by summing the values in your Income and Expense controls. It would look
something like:

= NZ(me.txt_Income, 0) + NZ(me.txt_Expense, 0)

I've used the NZ() function (Null to Zero) to ensure that if the value of
either textbox is NULL, it will treat it like a zero.

HTH
Dale

 

[Guest]

I am not sure if I have explained myself correctly.

Firstly –

“When you say "field" below are you actually talking about textbox controls
on your form?†Yes. This is being done on a subform.

Firstly, I tried what you said and in the Balance field I got #Name? I used
the following: =NZ(me.txt_Income,0)+NZ(me.txt_Expense,0) Should I have
replaced "me" with something else? The controls are named Income and Expense.

I think the problem revolves around the records. The subform looks like this

Date Description Amount Expense Income Balance
8/1/2004 Grant check $18,000.00 $18,000.00
#Name?
8/15/2004 Purchase Tickets ($16,538.60) ($16,538.60) #Name?
8/15/2004 Director Stipend $20,000.00 $20,000.00 #Name?

The problem is that I don’t want Income to subtract Expense because in each
record there is only one entry – either Income or Expense. (If the Amount is
positive it shows up also under Income: if negative, under Expense) I am
looking for something that will create a running balance – the way CCur does
on a report.

Am I tilting at windmills? I can get a Final Balance figure by putting in
this as the data source: =Sum([Amount]) but I end up with that figure running
all the way down that column of the subform. Or is there a way to have the
control be invisible on the subform and get that figure to show up on a
control on the main form? I tried putting in a new control in the main form
and putting =([Copy of SFAccountability].Form![Final Balance]) as the data
source but I get the same #Name? error there. (I am working on a copy of the
subform so that I don't mess things up too much.)

I hope this helps to explain my problem.

Mary

Mary,

DSUM is used to compute the sum of values in a column of a table. I'm not
entirely sure that is what you want to do here. If it is, you are on the
right track, but probably need to leave off the last comma in your DSUM
expression. It is only necessary if the optional criteria argument is being
passed.

When you say "field" below are you actually talking about textbox controls
on your form? If so, you can compute the value in your txt_Balance control
by summing the values in your Income and Expense controls. It would look
something like:

= NZ(me.txt_Income, 0) + NZ(me.txt_Expense, 0)

I've used the NZ() function (Null to Zero) to ensure that if the value of
either textbox is NULL, it will treat it like a zero.

HTH
Dale

I have a subform which I want to show grant money received and spent and a
running balance. I have been able to accomplish this on a report using the
CCur function.

On the subform (based on a table Accountability) I have an Amount field in
which I enter the amount either positive or negative. If the Amount is
positive it also shows up in a calculated field labeled Income; if the
Amount
is negative it shows up in a calculated field labeled Expense. Both the
Income and Expense field are locked. I have tried using a DSum function in
a
calculated Balance field but to date have not been successful.

I have never used this function before so my Syntax may be faulty. I have
tried the following:

=DSum("[Amount]","[Accountability]",)

In the above Amount is the field (positive or negative) Accountability is
the name of the underlying table of the subform. I have not included a
third
piece of the argument because I am not looking for a particular category -
e.g. what was spent on stationery.

Can anyone solve my riddle? Any help much appreciated.

Mary

 

[Marshall Barton]

Yes the word "field" is often used ambiguously. A Field is
a column in a table/query. The things on a form/report are
Controls.

The Me object refers to the class object's code module whare
the code is running. It is strictly a VBA item and is
unknown in a query or in the control's expression (where you
joust use the name of a field or another control.

It's too bad you didn't provide the expected values in the
Balance column. I don't see how Dale's expression can do
what you want, but then I don't quite understand what you do
want in the Balance column. If you're looking for the form
equivalent of the Report RunningSum property, there is no
such thing. so it can get to be a tricky issue. Even if it
were not such a messy problem, the standard kind of running
sum requires that you have a way to unambiguously sort the
records so the calculation can uniquely determine when to
calculate each record's value.

Using a DSum might do what you want, but it might also have
unacceptable performace. Try setting the Balance text box
to use an expression like:
=DSum("Amount", "thetable", "amtdate <= #" & amtdate & "#")
but that is incomplete because you may have some filtering
criteria in the form's record source query and those will
have to be included along with an additional criteria to
resolve the order of records with the same date value.

The grand total can be calculated in the subform's header or
footer by using the expression =SumAmount), bbut it not
going to help you the Balance column. The main report can
display that grand total value by using an expression that
refers back to the subform's GrandTotal control.
=subform.Form.GrandTotal
--
Marsh
MVP [MS Access]

I am not sure if I have explained myself correctly.

Firstly –

“When you say "field" below are you actually talking about textbox controls
on your form?” Yes. This is being done on a subform.

Firstly, I tried what you said and in the Balance field I got #Name? I used
the following: =NZ(me.txt_Income,0)+NZ(me.txt_Expense,0) Should I have
replaced "me" with something else? The controls are named Income and Expense.

I think the problem revolves around the records. The subform looks like this

Date Description Amount Expense Income Balance
8/1/2004 Grant check $18,000.00 $18,000.00
#Name?
8/15/2004 Purchase Tickets ($16,538.60) ($16,538.60) #Name?
8/15/2004 Director Stipend $20,000.00 $20,000.00 #Name?

The problem is that I don’t want Income to subtract Expense because in each
record there is only one entry – either Income or Expense. (If the Amount is
positive it shows up also under Income: if negative, under Expense) I am
looking for something that will create a running balance – the way CCur does
on a report.

Am I tilting at windmills? I can get a Final Balance figure by putting in
this as the data source: =Sum([Amount]) but I end up with that figure running
all the way down that column of the subform. Or is there a way to have the
control be invisible on the subform and get that figure to show up on a
control on the main form? I tried putting in a new control in the main form
and putting =([Copy of SFAccountability].Form![Final Balance]) as the data
source but I get the same #Name? error there. (I am working on a copy of the
subform so that I don't mess things up too much.)

DSUM is used to compute the sum of values in a column of a table. I'm not
entirely sure that is what you want to do here. If it is, you are on the
right track, but probably need to leave off the last comma in your DSUM
expression. It is only necessary if the optional criteria argument is being
passed.

When you say "field" below are you actually talking about textbox controls
on your form? If so, you can compute the value in your txt_Balance control
by summing the values in your Income and Expense controls. It would look
something like:

= NZ(me.txt_Income, 0) + NZ(me.txt_Expense, 0)

I've used the NZ() function (Null to Zero) to ensure that if the value of
either textbox is NULL, it will treat it like a zero.

I have a subform which I want to show grant money received and spent and a
running balance. I have been able to accomplish this on a report using the
CCur function.

On the subform (based on a table Accountability) I have an Amount field in
which I enter the amount either positive or negative. If the Amount is
positive it also shows up in a calculated field labeled Income; if the
Amount
is negative it shows up in a calculated field labeled Expense. Both the
Income and Expense field are locked. I have tried using a DSum function in
a
calculated Balance field but to date have not been successful.

I have never used this function before so my Syntax may be faulty. I have
tried the following:

=DSum("[Amount]","[Accountability]",)

In the above Amount is the field (positive or negative) Accountability is
the name of the underlying table of the subform. I have not included a
third
piece of the argument because I am not looking for a particular category -
e.g. what was spent on stationery.