Sum Range

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

Guest

Hi, is there a way to limit a running sum to the previous 12 or so records?
For example, in Excel I can use =sum(a1:a12) in cell b12. Then copy down that
formula into cells b13 thru b24 automatically changing the 1 and 12 to 2 and
13, 3 and 14, 4 and 15 and so on always summing the last 12 values.

Thanks,
Mike
 
No and Yes.

Access stores record in a big bucket. When you view a set of records, the
record number is for that display but is not related to the record itself.
With a different sort or criteria the records will indicate a different
record number.

You must have something to distinguish the 'last 12 records' like a date or
tracking number sequence the increments.

You will need to sort descinding on that sequence and add to the SELECT
statement like this --
SELECT Top 12 YourField1, YourField2, ....
 
Here, try this.

Place this code in your report module.

'**************************************************************************************Begin
code
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Good old variables
Dim i As Integer, tempSum As Integer

'An array that doesn't go away! Stores exactly 12 values
Static myNumbers(0 To 11) As Integer

'Move all previously stored record values "up" in the array and
'append the new value to the end.
For i = 0 To UBound(myNumbers) - 1
myNumbers(i) = myNumbers(i + 1)
Next i
myNumbers(UBound(myNumbers)) = Me.testdata

'Add up the total contained in the array
tempSum = 0
For i = 0 To UBound(myNumbers)
tempSum = tempSum + myNumbers(i)
Next i

'Display the result. I chose a label, you could just as easily choose
a text box.
Me.RunningSumLabel.Caption = tempSum
End Sub
'**************************************************************************************End
code

You may want to check out some info on the on_format event. . . I
think I'm forgetting something about checking that FormatCount
parameter which may give you some bugs if a page continues.

Other than that, this code is not bullet proof and will work for
simple reports. Don't go paging through a 300 page report and expect
it to keep track of the records correctly - it will let you down!

-Kris
 
Thank you!

A couple questions though, as I am still new to VB and many years have past
since my C++ college courses.

Wouldn't this need to be a 'Function' instead of a 'Sub', as it's returning
a value?

What's the purpose of the two arguments 'Cancel' and 'FormatCount'?

And wouldn't 'Me.testdata' need to be passed through as an argument?

Thanks,
Mike
 
Wouldn't this need to be a 'Function' instead of a 'Sub', as it's returning
a value?
What's the purpose of the two arguments 'Cancel' and 'FormatCount'?
And wouldn't 'Me.testdata' need to be passed through as an argument?
Click to expand...

The format event of the detail section has a special signature:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

You do not need to pass in Me.testdata as if it is in the detail
section, it is available to the format event (without need to declare
or pass).

As modification of Me.testdata can happen directly in the event
subroutine, there is no need to return data.

-Kris
 
Thanks!

Got it to work.

krissco said:
The format event of the detail section has a special signature:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

You do not need to pass in Me.testdata as if it is in the detail
section, it is available to the format event (without need to declare
or pass).

As modification of Me.testdata can happen directly in the event
subroutine, there is no need to return data.

-Kris
Click to expand...
 
'Display the result. I chose a label, you could just as easily choose
a text box.
Me.RunningSumLabel.Caption = tempSum
Click to expand...


How do I display the result in a text box?

Mike
 
Back
Top