Coding Question

[NavyJoe]

I got this code off of microsoft.com. It is the module you need to have a running sum in a query. I can't figure some stuff out:

Sample fields (told to put in Query):
CategoryID
UnitsInStock
RunSum: fncRunSum([CategoryID], [UnitsInStock])

Then, put the following code in a module:

-------------------------------------------
Option Compare Database
Option Explicit

Function fncRunSum(lngCatID As Long, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long

If lngID <> lngCatID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If

'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function
--------------------------------------------

My actual fields are:
Medal Types (same as CategoryID)
Qty (same as UnitsInStock)

In the module coding, how would I change the code for those fields? I tried, but it errors.

 

[nullZero]

The function should work as provided by Microsoft without changes, unless your medal type is text and not a number like in the MS example. If this is the case use the following...

Function fncRunSum(strMedalType As String, lngQty As Long) As Long
'Variables that retain their values.
Static strMedalID As String
Static lngAmt As Long

If strMedalID <> strMedalType Then
'If the current ID does not match the last ID, then (re)initialize.
strMedalID = strMedalType
lngAmt = lngQty
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngQty
End If

'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function