Running Sum in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that needs to have a running sum based on the information
from a field in the same section. For example, if the field named [Section
Label] = "Net Sales", I need the field [CY Section Total] to be a running sum
over group. If not, then it should not. Both of these fields are text
boxes.

The code below returns an error 2448 that the a value cannot be assigned to
this object.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
If Me![Section Label] = "Net Compensation" Then
Me![CY Section Total].RunningSum = 1
End If
End Sub

I've tried putting the code in different Events, but still get the same
error. The syntax seems right as I can use the code in the immediate window
and see the current value of the runningsum property. Help says I should be
able to do what I want since I can see code for it there
(Reports!rptSales!SalesTotal.RunningSum = 2)

I even tried creating a report with only one field and tried to change the
property with code. I received the same error.

As a work around, I tried creating two controls, one that had running sum
set to No and other set to Over Group. A third control used an iif statement
to reference one or the other based on the value in [Section Label]. That
didn't work because since the third control had running sum set to No, it
never changed
 
You should not need to use any code for this. Create a text box and set its
control source to:

=Abs([Section Label]="Net Sales") * [FieldYouWantSummed]

Set the running sum property of this text box to Over All.
 
Thank you for the suggestion. If the [Section Label] doesn't display the
text for which we are testing, the field returns a zero. I need it to return
the value (which happens to be a summed value) just not a running sum of the
value.

For example, if the text shows "Sales", the field should show the sum of
everything in the sales group. When the text shows "Net Sales", it should be
the running sum (Sum of Sales and Sum of Net Sales). The next group will be
"Cost of Goods". The field should go back to showing the sum of Cost of
Goods only. Might there be another solution that would help me?



Duane Hookom said:
You should not need to use any code for this. Create a text box and set its
control source to:

=Abs([Section Label]="Net Sales") * [FieldYouWantSummed]

Set the running sum property of this text box to Over All.

--
Duane Hookom
MS Access MVP
--

Mercedes said:
I have a report that needs to have a running sum based on the information
from a field in the same section. For example, if the field named
[Section
Label] = "Net Sales", I need the field [CY Section Total] to be a running
sum
over group. If not, then it should not. Both of these fields are text
boxes.

The code below returns an error 2448 that the a value cannot be assigned
to
this object.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
If Me![Section Label] = "Net Compensation" Then
Me![CY Section Total].RunningSum = 1
End If
End Sub

I've tried putting the code in different Events, but still get the same
error. The syntax seems right as I can use the code in the immediate
window
and see the current value of the runningsum property. Help says I should
be
able to do what I want since I can see code for it there
(Reports!rptSales!SalesTotal.RunningSum = 2)

I even tried creating a report with only one field and tried to change the
property with code. I received the same error.

As a work around, I tried creating two controls, one that had running sum
set to No and other set to Over Group. A third control used an iif
statement
to reference one or the other based on the value in [Section Label]. That
didn't work because since the third control had running sum set to No, it
never changed
Click to expand...
Click to expand...
 
How about providing some sample records and how exactly you would like them
to display in your report.

--
Duane Hookom
MS Access MVP


Mercedes said:
Thank you for the suggestion. If the [Section Label] doesn't display the
text for which we are testing, the field returns a zero. I need it to
return
the value (which happens to be a summed value) just not a running sum of
the
value.

For example, if the text shows "Sales", the field should show the sum of
everything in the sales group. When the text shows "Net Sales", it should
be
the running sum (Sum of Sales and Sum of Net Sales). The next group will
be
"Cost of Goods". The field should go back to showing the sum of Cost of
Goods only. Might there be another solution that would help me?



Duane Hookom said:
You should not need to use any code for this. Create a text box and set
its
control source to:

=Abs([Section Label]="Net Sales") * [FieldYouWantSummed]

Set the running sum property of this text box to Over All.

--
Duane Hookom
MS Access MVP
--

Mercedes said:
I have a report that needs to have a running sum based on the
information
from a field in the same section. For example, if the field named
[Section
Label] = "Net Sales", I need the field [CY Section Total] to be a
running
sum
over group. If not, then it should not. Both of these fields are text
boxes.

The code below returns an error 2448 that the a value cannot be
assigned
to
this object.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As
Integer)
If Me![Section Label] = "Net Compensation" Then
Me![CY Section Total].RunningSum = 1
End If
End Sub

I've tried putting the code in different Events, but still get the same
error. The syntax seems right as I can use the code in the immediate
window
and see the current value of the runningsum property. Help says I
should
be
able to do what I want since I can see code for it there
(Reports!rptSales!SalesTotal.RunningSum = 2)

I even tried creating a report with only one field and tried to change
the
property with code. I received the same error.

As a work around, I tried creating two controls, one that had running
sum
set to No and other set to Over Group. A third control used an iif
statement
to reference one or the other based on the value in [Section Label].
That
didn't work because since the third control had running sum set to No,
it
never changed
Click to expand...
Click to expand...
Click to expand...
 
Accounts Description Amt
Wages Total Compensation 500
Fringe Total Compensation 600
Temps Total Compensation 800
Bonuses Total Compensation 12
Chargebacks Net Compensation -300
Advertising Total Expenses 300
Travel & Entertainment Total Expenses 600
Meetings & Confs Total Expenses 900
Telephone Total Expenses 800
Other Total Expenses 400
Consultant Fees Total Expenses 100

The Accounts and Amounts Fields are used in the detail section of the
report. The description is used as a group footer. For the first group's
total "Total Compensation" should display with a sum of 1912 (=sum(Amt)).
For the next group, "Net Compensation" should display with a total of 1612
(=sum(amt) with running sum set to over group). For the last group, "Total
Expenses" should be 3100 (=sum(Amt) - no running sum).

Duane Hookom said:
How about providing some sample records and how exactly you would like them
to display in your report.

--
Duane Hookom
MS Access MVP


Mercedes said:
Thank you for the suggestion. If the [Section Label] doesn't display the
text for which we are testing, the field returns a zero. I need it to
return
the value (which happens to be a summed value) just not a running sum of
the
value.

For example, if the text shows "Sales", the field should show the sum of
everything in the sales group. When the text shows "Net Sales", it should
be
the running sum (Sum of Sales and Sum of Net Sales). The next group will
be
"Cost of Goods". The field should go back to showing the sum of Cost of
Goods only. Might there be another solution that would help me?



Duane Hookom said:
You should not need to use any code for this. Create a text box and set
its
control source to:

=Abs([Section Label]="Net Sales") * [FieldYouWantSummed]

Set the running sum property of this text box to Over All.

--
Duane Hookom
MS Access MVP
--

I have a report that needs to have a running sum based on the
information
from a field in the same section. For example, if the field named
[Section
Label] = "Net Sales", I need the field [CY Section Total] to be a
running
sum
over group. If not, then it should not. Both of these fields are text
boxes.

The code below returns an error 2448 that the a value cannot be
assigned
to
this object.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As
Integer)
If Me![Section Label] = "Net Compensation" Then
Me![CY Section Total].RunningSum = 1
End If
End Sub

I've tried putting the code in different Events, but still get the same
error. The syntax seems right as I can use the code in the immediate
window
and see the current value of the runningsum property. Help says I
should
be
able to do what I want since I can see code for it there
(Reports!rptSales!SalesTotal.RunningSum = 2)

I even tried creating a report with only one field and tried to change
the
property with code. I received the same error.

As a work around, I tried creating two controls, one that had running
sum
set to No and other set to Over Group. A third control used an iif
statement
to reference one or the other based on the value in [Section Label].
That
didn't work because since the third control had running sum set to No,
it
never changed
Click to expand...
Click to expand...
Click to expand...
 
I would create a higher up grouping on
=Instr([Description],"Compensation")>0
Then you can add group footers for this to display 1612 and 3100.

--
Duane Hookom
MS Access MVP
--

Mercedes said:
Accounts Description Amt
Wages Total Compensation 500
Fringe Total Compensation 600
Temps Total Compensation 800
Bonuses Total Compensation 12
Chargebacks Net Compensation -300
Advertising Total Expenses 300
Travel & Entertainment Total Expenses 600
Meetings & Confs Total Expenses 900
Telephone Total Expenses 800
Other Total Expenses 400
Consultant Fees Total Expenses 100

The Accounts and Amounts Fields are used in the detail section of the
report. The description is used as a group footer. For the first group's
total "Total Compensation" should display with a sum of 1912 (=sum(Amt)).
For the next group, "Net Compensation" should display with a total of 1612
(=sum(amt) with running sum set to over group). For the last group,
"Total
Expenses" should be 3100 (=sum(Amt) - no running sum).

Duane Hookom said:
How about providing some sample records and how exactly you would like
them
to display in your report.

--
Duane Hookom
MS Access MVP


Mercedes said:
Thank you for the suggestion. If the [Section Label] doesn't display
the
text for which we are testing, the field returns a zero. I need it to
return
the value (which happens to be a summed value) just not a running sum
of
the
value.

For example, if the text shows "Sales", the field should show the sum
of
everything in the sales group. When the text shows "Net Sales", it
should
be
the running sum (Sum of Sales and Sum of Net Sales). The next group
will
be
"Cost of Goods". The field should go back to showing the sum of Cost
of
Goods only. Might there be another solution that would help me?



:

You should not need to use any code for this. Create a text box and
set
its
control source to:

=Abs([Section Label]="Net Sales") * [FieldYouWantSummed]

Set the running sum property of this text box to Over All.

--
Duane Hookom
MS Access MVP
--

I have a report that needs to have a running sum based on the
information
from a field in the same section. For example, if the field named
[Section
Label] = "Net Sales", I need the field [CY Section Total] to be a
running
sum
over group. If not, then it should not. Both of these fields are
text
boxes.

The code below returns an error 2448 that the a value cannot be
assigned
to
this object.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As
Integer)
If Me![Section Label] = "Net Compensation" Then
Me![CY Section Total].RunningSum = 1
End If
End Sub

I've tried putting the code in different Events, but still get the
same
error. The syntax seems right as I can use the code in the
immediate
window
and see the current value of the runningsum property. Help says I
should
be
able to do what I want since I can see code for it there
(Reports!rptSales!SalesTotal.RunningSum = 2)

I even tried creating a report with only one field and tried to
change
the
property with code. I received the same error.

As a work around, I tried creating two controls, one that had
running
sum
set to No and other set to Over Group. A third control used an iif
statement
to reference one or the other based on the value in [Section Label].
That
didn't work because since the third control had running sum set to
No,
it
never changed
Click to expand...
Click to expand...
Click to expand...
 
Back
Top