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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,052
Messages
5,835,146
Members
430,342
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top