Using Excel functions within Access

[webmagic]

Hi,
I know I have seen sveral examples but I can't seem to get them to work. I would like to get the quartile function from excel to work on a table or query. I have played with calling other excel functions and they are working. But when it comes to getting the array into a function for the quartile function it doesn't work.

Your help, expertise and direction is greatly appreciated.

Thank you,
Anne

 

[chergh]

Can you post the code you do have?

 

[webmagic]

I found this on the internet and figure if I get this working I can get the quartile working.


Public Function Percentile(strTbl As String, strFld As String, k As Double) As Double
Dim rst As ADODB.Recordset
Dim dblData() As Double
Dim xl As Object
Dim x As Integer
Set xl = CreateObject("Excel.Application")
Set rst = New ADODB.Recordset
rst.Open "Select * from " & strTbl, CurrentProject.Connection, adOpenStatic
ReDim dblData(rst.RecordCount - 1)
For x = 0 To (rst.RecordCount - 1)
dblData(x) = rst(strFld)
rst.MoveNext
Next x
Percentile = xl.WorksheetFunction.Percentile(dblData, k)
rst.Close
Set rst = Nothing
Set xl = Nothing
End Function

 

[chergh]

The code looks fine. What error message are you getting when you try to use the function?

 

[webmagic]

I think I got it now

It appeared to be in the way I was calling it.


Private Sub Command6_Click()
MsgBox Percentile("data_table", "data", 0.5)
End Sub

 

[webmagic]

Just posting did it

Thank you.
I think just posting did the magic. I have been working on this for two days-and poof after posting-issue resolved. Thanks for offering a place like this where magic happens!!!!

 

[georgedwilkinson]

Glad we were able to help.

 

[webmagic]

now trying to call in a query

Hello,
I am placing the following code in the query and am getting the error "Undefined function 'Quartile' in expression.

quartile1: Quartile([data_table],[1])


I was trying to place the results in a table field for quartile 1 in quartile1 in the same table as the data. Any help is greatly appreciated.