Copy the following code into a module and save it (Make sure you don't name
the module the same as the function!)
'*********************** Start of Code *************************
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
Set rsMedian = Nothing
Set dbMedian = Nothing
Exit Function
Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian
End Function
'************************ End of Code **************************
To calculate the median value of Field1 in TableA, you'd use:
=DMedian("Field1", "TableA")
To calculate the median value of Field1 in TableA, but only for those where
Field2 has a value of 6, you'd use:
=DMedian("Field1", "TableA", "Field2 = 6")
(Warning: I haven't tested this exhaustively, but I'm reasonably confident
it's correct)
