Function Variables

  • Thread starter Thread starter Forrest
  • Start date Start date
F

Forrest

How would one replace the string of numbers in the
following code with a field reference to an Access table?

The following code excerpt is from:
http://support.microsoft.com/default.aspx?scid=kb;EN-
GB;q198571


Sub xlMedian()
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
MsgBox objExcel.Application.Median(1, 2, 5, 8, 12, 13)
objExcel.Quit
Set objExcel = Nothing
End Sub

The following modification addresses the first record. I
need the median of all records.

Private Sub xlMedian_Click()
Dim objExcel As Excel.Application
Dim dbs As Database
Dim rst As Recordset
Set objExcel = CreateObject("Excel.Application")
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Investments")
MsgBox objExcel.Application.WorksheetFunction.Median
(rst![Amount])
objExcel.Quit
Set objExcel = Nothing

End Sub
 
Forrest:

You don't have to use Excel to get a median in Access. Stop by our web and
look in the Code and Design Tips area under Queries for a function that you
can easily import into your db to use with a table or recordset to determine
medians or percentiles.

HTH
 
Back
Top