Function MedianF(pTable As String, pfield As String) As Single
'*******************************************
'Purpose: Return median value from a recordset
'Coded by: raskew
'Inputs: ? medianF("Orders", "Freight") <enter.
'Output: 41.36 (may vary according to hom much
' you've fiddled with this table).
'*******************************************
Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
Dim sglHold As Single
strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.Move -Int(n / 2)
If n Mod 2 = 1 Then 'odd number of elements
MedianF = rs(pfield)
Else 'even number of elements
sglHold = rs(pfield)
rs.MoveNext
sglHold = sglHold + rs(pfield)
MedianF = sglHold / 2
End If
rs.Close
End Function