I am trying to use Excel's Percentile Function is Access by using the following code I found on the internet:

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

However in my query design when I reference the following:

Expr1: percentile("test table name","test column",25)

I get a syntax error that I cannot resolve.

Has anybody used this function in Access before.

Any help is greatly appreciated

Paul

Paul,

What exactly are you trying to do?

What data is in your table?

Kevin

dartcoach

Kevin, I was trying to get the 25th percentile (k) from a column of numbers (strTbl As String, strFld As String) similar to the way the percentile function is used in Excel. The code I was using actually did work. I just had to move the bolded areas to the end of the line above them.


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

However I am now trying to use the same method of calling the excel funcion percentrank into Access and get the following error message:

Run-Time error '1004'
Unable to get the PercentRank property of the WorksheetFunction class

If anyone can provide me any insights as to what this message means I will be grateful.

Paul

You might have to open the "xl" object to access the function.

Dear colleagues,

I had the same error and I decided to took the code above and developed further upon that, using VB only and no external call to Excel.

I developed another solution before, using "DCount" function, but it was rather slow...

The code below works and it proved to be very fast (50x faster than the "DCount" implementation indeed...)

It creates a static copy of the data to be evaluated in memory and does all the job upon that copy.

Enjoy! (feedbacks are welcome)

Fernando Cesar
[email protected]

-------------------------------------------------------------
Public Function PRank(Tbl As String, Fld As String, Value As Double) As Double

    Static Data() As Double
    Static RecCount, FirstRunCompleted, LastRun As Integer
    Dim x, y As Integer

    If FirstRunCompleted = 0 Then
        Dim rst As ADODB.Recordset
        Set rst = New ADODB.Recordset
        rst.Open "Select " & Fld & " from " & Tbl & _
        " Group by [" & Tbl & "]." & Fld & _
        " Order by [" & Tbl & "]." & Fld & " DESC", _
        CurrentProject.Connection, adOpenStatic

        RecCount = rst.RecordCount
        ReDim Data(RecCount - 1)
        For x = 0 To (RecCount - 1)
            Data(x) = rst(Fld)
            rst.MoveNext
        Next x
        FirstRunCompleted = 1
        rst.Close
        Set rst = Nothing
    End If

    For x = 0 To (RecCount - 1)
        If Data(x) = Value Then
            y = x + 1
            Exit For
        End If
    Next x

    If x = (RecCount - 1) Then
        LastRun = 1
    End If

    PRank = (RecCount - y) / (RecCount - 1)

    If LastRun = 1 Then
        ReDim Data(0)
        FirstRunCompleted = 0
        LastRun = 0
        RecCount = 0
        Exit Function
    Else
        Exit Function
    End If

End Function

I think your problem may be in this line:

rst.Open "Select * from " & strTbl, CurrentProject.Connection,
adOpenStatic

There is no variable declaration for strTbl.
If you are trying to reference a table name in this line, you will always have problems. Here is a better way to do it:

Dim strTbl As String
Dim sSQL as String

strTbl = "tblTextTableName" 'don't use spaces in Access objects

sSQL = "Select * from " & strTbl

rst.Open sSQL, CurrentProject.Connection, adOpenStatic

Alternatively, you can drop the strTbl variable and create your SQL string as:

sSQL = "Select * from tblTestTableName"

That should prevent Access from throwing the weird errors it does when you try to build a SQL string in the rst.Open line.

Does this help?


mmcdonal

Hello Fernando,

I was trying to understand how to use the Excel Function PercentageRank when I saw your post at http://p2p.wrox.com/topic.asp?TOPIC_ID=41465
I am not a programmer but I need to rank some University resources with the Excel Function PercentageRank within a MS Access Database.
I tried to use your code by creating a module:

-----------------------------------------------------------
Public Function PRank(Tbl As String, Fld As String, Value As Double) As Double

    Static Data() As Double
    Static RecCount, FirstRunCompleted, LastRun As Integer
    Dim x, y As Integer

    If FirstRunCompleted = 0 Then
        Dim rst As ADODB.Recordset
        Set rst = New ADODB.Recordset
        rst.Open "Select " & Fld & " from " & Tbl & _
        " Group by [" & Tbl & "]." & Fld & _
        " Order by [" & Tbl & "]." & Fld & " DESC", _
        CurrentProject.Connection, adOpenStatic

        RecCount = rst.RecordCount
        ReDim Data(RecCount - 1)
        For x = 0 To (RecCount - 1)
            Data(x) = rst(Fld)
            rst.MoveNext
        Next x
        FirstRunCompleted = 1
        rst.Close
        Set rst = Nothing
    End If

    For x = 0 To (RecCount - 1)
        If Data(x) = Value Then
            y = x + 1
            Exit For
        End If
    Next x

    If x = (RecCount - 1) Then
        LastRun = 1
    End If

    PRank = (RecCount - y) / (RecCount - 1)

    If LastRun = 1 Then
        ReDim Data(0)
        FirstRunCompleted = 0
        LastRun = 0
        RecCount = 0
        Exit Function
    Else
        Exit Function
    End If

End Function
-----------------------------------------------------------


I then tried to call the module within a query with the following expression:

---------------------------------------------
Expr1: PRank([SITES],[SITES]![AlexaRank],2)
---------------------------------------------

Where [SITES] is the Table
[SITES]![AlexaRank] is the field with the values
 And 2 is the Value (I guess the number of decimal places)


When I try to run the query, however I get the following error:

"Undefined Function "Prank" in expression"

The Prank module is however saved and present so I do not understand why I get this error

Should you know any other way to use the Excel Function PercentageRank, I would really appreciate.

Thank you for your help.

FF

Hello, Fabio,
I use to get the same error from time to time, with many functions I create...
I really don't know why this happens, but saving all the stuff, closing Access and opening it again seems to work (not 100%). Other trick I use is to try to change character case when calling the function ("prank" instead of "PRank"). It's really weird - I agree -and maybe another colleague here at the forum could help us both!

Now a very IMPORTANT warning: I found out the function I posted before has a problem: it cannot be called more than once in the same query and it LEAVES GARBAGE in memory!! So you need to close Access and open it again in order to use the function again.. which does not represent good coding at all :(

If you want to use a code that really works, despite having a quite slow execution, please consider this other code of mine: