Running Sum in VBA

[Mercedes]

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

 

[Duane Hookom]

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.

 

[Mercedes]

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

 

[Duane Hookom]

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

--
Duane Hookom
MS Access MVP

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