median in access query

[C111]

hi,
I am trying to calculate the median in an access query. I tried various forums and I can never get it to work...

I created a table called: "Ages"
In the table called "Ages", I have one column called "Names" and it contains a couple of names. I also have one column called "Age".

I created a module and wrote:

Option Compare Database
Option Explicit
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


And then I created a query: Expr1: MedianF("Age","Ages") and labelled it as an expression but it doesn't work.

Please can you help?
Thanks,
Charlotte

 

[alansidman]

How about this Charlotte

Finding the Median Value for Fields in Access 2007