Public Function MedianOfRst(RstName As String, fldName As String, optional groupByList as string) As Double
'This function will calculate the median of a recordset. The field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
'** NEW CODE ADDED BY GIACOMO **
Dim strSQL as string
strSQL = "SELECT [" & fldName & "] FROM [" & RstName & "]"
if groupByList not null then
strSQL = strSQL & " GROUP BY " & groupByList
end if
Set RstOrig = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
'** END OF NEW CODE **
' ** OLD CODE REPLACED IN THE BLOCK ABOVE **
'Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
' ** ORIGINAL CODE **
RstOrig.Sort = fldName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
MedianTemp = RstSorted.Fields(fldName).Value
RstSorted.MoveNext
MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
MedianTemp = MedianTemp / 2
Else
RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
MedianTemp = RstSorted.Fields(fldName).Value
End If
MedianOfRst = MedianTemp
End Function