Median average

[tezread]

I have set up a module using the Microsoft sample:
Function Median(tName As String, fldName As String) As Single
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer
Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "];")
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName & "] IS NOT NULL from the example.
ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x = RCount Mod 2
If x <> 0 Then
OffSet = ((RCount + 1) / 2) - 2
For i% = 0 To OffSet
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For i = 0 To OffSet
ssMedian.MovePrevious
Next i
x = ssMedian(fldName)
ssMedian.MovePrevious
y = ssMedian(fldName)
Median = (x + y) / 2
End If
If Not ssMedian Is Nothing Then
ssMedian.Close
Set ssMedian = Nothing
End If
Set MedianDB = Nothing
End Function


and run a query

SELECT Median([ReferralToTreatment]) AS Expr1
FROM qryEpisode
GROUP BY Median([ReferralToTreatment]);

but the error message tells me I have te wrong numbe rof arguments.
I am trying to get a median average of ReferraltoClinicDate

 

[plog]

A wrong number of arguments error means you are not passing the function enough data. Here's the function declaration line:

Function Median(tName As String, fldName As String) As Single


What that tells you is that the function is called Median, you must pass it 2 strings of information (aka arguments) and it returns a value whose type is a Single. The two strings you must pass it look like the table name (tName) and the field name (fldName) of what you want to calculate the median of.

You are only passing it one variable (aka argument), thus the wrong number. My guess is your SQL needs to look like this:

SELECT Median("qryEpisode", "ReferralToTreatment") AS Expr1
FROM qryEpisode
GROUP BY Median("qryEpisode", "ReferralToTreatment");

 

[tezread]

works a treat plog thank you!

 

[vbaInet]

Here's another Median function just fyi:

http://www.access-programmers.co.uk/forums/showthread.php?t=202859&highlight=median