Public Function MedianOfRst(RstName As String, fldName As String, Optional strWhere As String) As Double
'** Call function in query by Median: MedianOfRst("Table Name","Field Name","[Group]= " & [Group])
'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 strWhere <> vbNullString Then
strSQL = strSQL & " WHERE " & strWhere
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