Here's a median function in access that you can call in any query. You
could change this to any excel function you wanted. Most people can
find the windows help file that says how to call an excel function but
don't know how to pass an array of the recordset they made into that
function. This uses GetRows that nicely creates an array that can be
passed into excel.
I have this data table for testing. The table name is TestData
Names Numbers
Charles 3
Henry 2.5
Henry 2
Henry 3
Henry 1
John 63
Ken 6
Ken 45
Ken 13
Lewis 5
Opi 9
Opi 45
Opi 9
Opi 36
Paul 1
Call in a query MyFunc: Median("TestData","Numbers","Names"," ='Ken' ")
Public Function Median(Table As String, TgtField As String, LimField As
Variant, Criteria As Variant) As Double
Dim mySQL As String
Dim rst As Recordset
Dim N As Long ' number of records
On Error GoTo 10000
If IsNull(LimField) Or IsNull(Criteria) Then
mySQL = "SELECT " & TgtField & " FROM " & Table & ";"
Else
mySQL = "SELECT " & TgtField & " FROM " & Table & " WHERE [" &
LimField & "] " & Criteria & "" & ";"
End If
Set rst = CurrentDb.OpenRecordset(mySQL, dbOpenDynaset)
rst.MoveLast 'you need this to force access to actually load
all of them.
N = rst.RecordCount
rst.MoveFirst 'getrows defaults to getting rows from your current
position (bookmark) to number of rows you specify (N) in our case which
is the total number of records.
Median = Excel.Application.Median(rst.GetRows(N)) 'You have to have
a reference to Microsoft Excel in your references in tools..references
rst.Close
GoTo 20000
10000:
MsgBox "Error in-----" & mySQL
'other way of using excel
'Dim obj As Excel.Application
' Set obj = CreateObject("Excel.Application")
' MsgBox obj.Application.Median(1, 2, 5, 8, 12, 13)
' obj.Quit
' Set obj = Nothing
20000:
End Function
If you like this send me an e-mail. I want to be famous.
