468,272 Members | 2,000 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,272 developers. It's quick & easy.

Median function in Access calling Excel worksheetfunction, or change to any excel function on a query recordset

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.

Jul 26 '06 #1
1 6164
If you like this send me an e-mail. I want to be famous.

For what? There's been code for this on MS's website for YEARS. Oh
well, so much for being famous.

Jul 27 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by nick.vitone | last post: by
3 posts views Thread by gunnarnime | last post: by
4 posts views Thread by uspensky | last post: by
3 posts views Thread by Scott | last post: by
16 posts views Thread by Phil Stanton | last post: by
6 posts views Thread by rrstudio2 | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.