-----Original Message-----
Hi Michael
Access does not have a Median function. However the following function will do
the job for you. tName$ is the table name and fldname$ is the field name
Hope this helps
Maurice St-Cyr
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
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
ssMedian.Close
MedianDB.Close
End Function
Does Access have a median funtion? I need to find the
median value of some sales data, and I have not been able
to find a median funtion.
Thanks
.
