using median function in a query!?

[kev777]

I am trying to find the median of a single set of values produced in a query in access, the code I am using is as follows:

Function FindMedian(rst As Recordset) As Double
Dim rst As Recordset
Dim intCount As Integer
Dim dblValue1 As Double
Dim dblValue2 As Double
Set rst = CurrentDb.OpenRecordset
rst.MoveLast
intCount As rst.RecordCount
If intCount Mod 2 = 0 Then
rst.AbsolutePosition = intCount / 2 - 1
dbvalue1 = rst!ValueColumn
rst.MoveNext
dbvalue2 = rst!ValueColumn
FindMedian = (dbvalue1 + dbvalue2) / 2
Else
rst.AbsolutePosition = (intCount + 1) / 2
FindMedian = rst!ValueColumn
End If
rst.Close
Set rst = Nothing
End Function

In the query I'm using Expr1: FindMedian([Query1]![data1])where data1 is the column of data whose median I am trying to find, but all I'm getting are errors when I run the query!

Please help - sorry, newbie!

Kev

 

[MichaelRed]

I'm not convinced that it is "right", but the below will at least return a response (other than err).

I would not approach the function in this manner, but it certainly is a "workable" soloution for relatively small recordsets.

Function basFindMedian(rst As Recordset, ColName As String) As Double

    Dim rst As Recordset
    Dim intCount As Integer
    Dim dblValue As Double

    rst.MoveLast
    intCount = rst.RecordCount

    If intCount Mod 2 = 0 Then
         rst.AbsolutePosition = intCount / 2 - 1
         dbvalue = rst(ColName)
         rst.MoveNext
         basFindMedian = dbvalue + rst(ColName)
    Else
         rst.AbsolutePosition = (intCount + 1) / 2
         basFindMedian = rst(ColName)
    End If

    rst.Close
    Set rst = Nothing

End Function
Function basTestFindMedian() As Double

    Dim rst As Recordset
    Set rst = CurrentDb("qryMedianContribution")
    basTestFindMedian = basFindMedian(rst, "Contribution")
End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over