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.
--
"Why live in the real world adhering to thier rules, when you can live in
code and write your own"
"Jason" wrote:
[Quoted Text] > 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
|