Coding Question

NavyJoe

Board Regular
Joined
Sep 14, 2004
Messages
63
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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

nullZero

Active Member
Joined
Nov 14, 2005
Messages
497
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,228
Messages
5,571,008
Members
412,353
Latest member
SofiaV
Top