Running sum on access query

  • Thread starter Thread starter Big B
  • Start date Start date
B

Big B

Hi There
I have been trying lot to calculate running sum on calculate field...
Below is my code but static variable doesnt get initialise when I
re-run query..And its also mess up when i click on running sum field or
scroll down or up query result...
From query i have two additional columns ..First column is just to
Click to expand...
initialise static variable to zero but it doesnt work when i re-run
query..

Dum: qryRunSum()
RunSum: qryRunSum([Result])

'Please note that [Result] is calculated field based on other fields of
query..

Public Function qryRunSum(Optional arg As Variant) As Double
Static lastrunsum As Double
If IsMissing(arg) Then
lastrunsum = 0
Else
lastrunsum = lastrunsum + arg
End If
qryRunSum = lastrunsum
End Function

Any help would be appreciated
Thanx in advance...
 
Another approach might be to run the query to return values without trying
to do the running sum. Then create a report based on the query and use a
textbox control WITH running sum set in the report, not the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Big B said:
Hi There
I have been trying lot to calculate running sum on calculate field...
Below is my code but static variable doesnt get initialise when I
re-run query..And its also mess up when i click on running sum field or
scroll down or up query result...
From query i have two additional columns ..First column is just to
Click to expand...
initialise static variable to zero but it doesnt work when i re-run
query..

Dum: qryRunSum()
RunSum: qryRunSum([Result])

'Please note that [Result] is calculated field based on other fields of
query..

Public Function qryRunSum(Optional arg As Variant) As Double
Static lastrunsum As Double
If IsMissing(arg) Then
lastrunsum = 0
Else
lastrunsum = lastrunsum + arg
End If
qryRunSum = lastrunsum
End Function

Any help would be appreciated
Thanx in advance...
Click to expand...
 
Report is best but...

For running sums it is easiest to use a report but for a module i'd attack it like this:

Static Function qryRunSum(ByVal dblArg As String)
Dim dblLastRun As Double

Select Case dblArg
Case "Clear"
dblLastRun = 0
Case Else
dblLastRun = dblLastRun + dblArg
End Select

qryRunSum = Nz(dblLastRun, 0)

End Function

Pass a "0" when you want the result or pass "Clear" if you want to reset the Function.

Add Number
qryRunSum 1.554

Reset Function
qryRunSum "Clear"

Return Value
x = qryRunSum(0)

Enjoy.
 
Back
Top