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