Hello,
I'm looking for a median function in Access for using it in querie
with "group by".
I did find a few examples of good VB-code, where I even can suppl
optional criteria. This way it is possible to use it on "grou
by"-queries.
They work fine, as long a I use the provided functions on tables o
queries without parameters.
However, I want to be able to use those median functions also on
query with parameters, as you can with the "Avg"-function provided b
Access. Anyone an idea how to do this?
Here is an example of such a median function I found (sorry, don'
remember the author...). I would like to use it with "TableName
referring to a query with parameters.

Public Function DMedian(FieldName As String, _
TableName As String, _
Optional Criteria As Variant) As Double
On Error GoTo Err_DMedian
'Returns the median of a given field in a given table.
'Returns -1 if no recordset is created

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim RowCount As Long
Dim LowMedian As Double, HighMedian As Double

'Open a recordset on the table.
Set db = CurrentDb
strSQL = "SELECT " & FieldName & " FROM " & TableName
If Not IsMissing(Criteria) Then
strSQL = strSQL & " WHERE " & Criteria & " ORDER BY "
FieldName
Else
strSQL = strSQL & " ORDER BY " & FieldName
End If
'    Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'Find the number of rows in the table.
rs.MoveLast
RowCount = rs.RecordCount
rs.MoveFirst
If RowCount Mod 2 = 0 Then
'There is an even number of records. Determine the low an
high
'values in the middle and average them.
rs.Move Int(RowCount / 2) - 1
LowMedian = rs(FieldName)
rs.Move 1
HighMedian = rs(FieldName)
DMedian = (LowMedian + HighMedian) / 2
Else
'There is an odd number of records. Return the value exactl
in
'the middle.
rs.Move Int(RowCount / 2)
DMedian = rs(FieldName)
End If

Exit_DMedian:
Exit Function

Err_DMedian:
If Err.Number = 3075 Then
DMedian = 0
Resume Exit_DMedian
ElseIf Err.Number = 3021 Then
'EOF or BOF ie no recordset created
DMedian = -1
Resume Exit_DMedian
Else
MsgBox Err.Description
Resume Exit_DMedian
End If
End Functio

--
stef.pillaer