Median Function for Access

Function DMedian(FieldName As String, _
TableName As String, _
Optional WhereClause As String = "" _
) As Single

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & FieldName & _
"] FROM [" & TableName & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
' strSQL = strSQL & "WHERE [" & FieldName & "] IS NOT NULL "
' If Len(WhereClause) > 0 Then
' strSQL = strSQL & "AND (" & WhereClause & ") "
' End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
If Len(WhereClause) > 0 Then
strSQL = strSQL & "WHERE " & WhereClause & " "
End If
strSQL = strSQL & "ORDER BY [" & FieldName & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(FieldName)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(FieldName)
rsMedian.MovePrevious
dblTemp2 = rsMedian(FieldName)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function