Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 15th, 2006, 12:38 PM
Registered User
 
Join Date: Mar 2006
Location: albuquerque, NM, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using Excel's Percentile Function is Access

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


Reply With Quote
  #2 (permalink)  
Old March 15th, 2006, 12:51 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Paul,

What exactly are you trying to do?

What data is in your table?

Kevin

dartcoach
Reply With Quote
  #3 (permalink)  
Old March 15th, 2006, 04:44 PM
Registered User
 
Join Date: Mar 2006
Location: albuquerque, NM, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old March 15th, 2006, 05:00 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #5 (permalink)  
Old August 31st, 2006, 11:53 AM
Registered User
 
Join Date: Aug 2006
Location: Sao Paulo, SP, Brazil.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to fncesar@uol.com.br
Default

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
fncesar@uol.com.br

-------------------------------------------------------------
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
Reply With Quote
  #6 (permalink)  
Old September 1st, 2006, 06:50 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old October 23rd, 2006, 12:24 PM
Registered User
 
Join Date: Oct 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old October 26th, 2006, 09:48 AM
Registered User
 
Join Date: Aug 2006
Location: Sao Paulo, SP, Brazil.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to fncesar@uol.com.br
Default

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:

Code:
Public Function PRank(TableName As String, FieldName As String, x As Double) As Double

Dim above, below, PRank As Double

On Error GoTo Err

    If x <= 0 Or IsNull(x) Then
        Exit Function
    End If

    above = DCount("[" & TableName & "]![" & FieldName & "]", "[" & TableName & "]", "[" & FieldName & "]>" & Replace(CStr(x), ",", "."))

    below = DCount("[" & TableName & "]![" & FieldName & "]", "[" & TableName & "]", "[" & FieldName & "]> 0 AND [" & FieldName & "]<" & Replace(CStr(x), ",", "."))

    PRank = below / (below + above)
    Exit Function

Err:
    MsgBox Err.Description
    Exit Function

End Function
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum function in Access form ayazhoda Access VBA 2 July 26th, 2007 04:26 PM
excel's yield function equivalent in visual basic thisisprem Beginning VB 6 2 April 25th, 2006 02:06 AM
Solution Excel's can't find the project or library Born2BFree Excel VBA 0 October 21st, 2005 11:49 AM
Instring function in ms access vishnushankar Access 2 December 24th, 2004 02:06 PM
auto function in access tables leeegglestone Access 0 August 12th, 2004 04:46 AM



All times are GMT -4. The time now is 09:40 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.