Running sum on access query

[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

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...

 

[Jeff Boyce]

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

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

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...

 

[MoloneyAccesser]

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.