Hey June7. I created the module and it ran fine, the problem is that it will return a running sum for each student (ID), but it does not distinguish between levels. When the student's level changes (like fron U to G) it keeps totaling and does not begin to start to calculate again from the new level. I tried to add a second group to the function [Function fncRunSum(lngCatID As Long, lngCatID2 as Long, lngUnits As Long) As Long] but do not know how to incorporate this new level into the rest of the module.
Is there a way to change the function to make it look at ID and Level, not just level?
You are right, the report is much easier! But I return the data in an Access table so that further analysis can be done by others. I believe they take my data (and data from other offices) and use it for regression analysis.
Code:
Function fncRunSum(lngCatID As Long, lngCatID2 As Long, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As Long
Static lngID2 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
Else
If lngID2 <> lngCatID2 Then
'If the current Level does not match the last Level, then (re)initialize.
lngID2 = lngCatID2
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If
End If
'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function