Percentile Function

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Greetings,

I am somewhat of a novice, so please bear with me. I am trying to come up
with a user defined function that will allow me to find the xth percentile on
a group of records within a query. Scouring the net, I found the code below.
I have put this into a module in my DB and have tested it using the
immediate window, with the returned results giving me what I expected.

However, if I then try to use the function within a textbox on a report,
when the report is run I get the error "Enter Parameter Value....
Percentile". Again, code itself seems to be working, but for some reason it
doesn't appear that it can find the Percentile function when I try to access
it from a report. Any ideas?

Thanks.

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

Private Sub test()
MsgBox Percentile("query", "field", 0.5)
End Sub
 
Have you tried building a query with the function call built into it, then
build your report off the query? I use customer defined functions for a lot
of my reports, and I've always built them into queries with no problems.
I've never tried using the function directly on a report though.
 
In the Control Source property for the textbox, make sure the = sign is
first, otherwise the report will think Percentile() is a bound field to the
report, which of course it is not and thus cannot be found.

=Percentile(tblName, fldName, k)

RJ

Database Whiz Consulting

www.databasewhiz.com
 
I wrote this yesterday, but it doesn't appear to have posted...

In the Control Source property for the textbox, make sure the = sign is
first, otherwise the report will think Percentile() is a bound field to the
report, which of course it is not and thus cannot be found.

=Percentile(tblName, fldName, k)

RJ
Database Whiz Consulting
www.databasewhiz.com
 
Back
Top