How do I correctly reference a VBA Public Function in a query?

Andrej

Registered User.
Local time
Today, 10:49
Joined
Oct 17, 2007
Messages
40
Hi all,

I'm trying to get a public function to work within the design view of a query. The function is defined as:

Public Function Percentile_(fldName As String, _
tblName As String, p As Double, _
Optional strWHERE As String = "") _
As Double... ( I can put the whole code if u need it)

Any thoughts about how I can make the Percentile function calculate values according to a GroupBy statement and not for whole data set?
 
The problem with a code is because it calculate a percentile value for the whole data set. I need values for the each group, that are grouped by the same name or number of other field.
I have to much groups that I can't make other queries to calculate from them

I don't know if I explain it well my problem

my code is:

Public Function Percentile(fldName As String, _
tblName As String, p As Double, _
Optional strWHERE As String = "") _
As Double

'Note tblName can also be the name of a query or view

'ADO
'Dim cnn As ADODB.Connection
'Dim rst As ADODB.Recordset
'DAO
Dim Cdb As Database
Dim rst As Recordset
'Other Vars
Dim break_pt As Double
Dim sqlSort As String
Dim low_obs As Long, high_obs As Long
Dim r1 As Double, r2 As Double, x As Double
Dim N As Long
Dim recno As Long


'VERIFY VALID PERCENTILE (0-100) WAS GIVEN
If (p <= 0 Or p >= 100) Then
Percentile = -555555555 'Something to stick out!
Exit Function
End If

'ENSURE DESIRED DATA IS SORTED
If Len(strWHERE) < 1 Then
sqlSort = "SELECT [" & fldName & "] " & _
"FROM [" & tblName & "] " & _
"ORDER BY [" & fldName & "]"
Else
sqlSort = "SELECT [" & fldName & "] " & _
"FROM [" & tblName & "] WHERE " & _
strWHERE & _
" ORDER BY [" & fldName & "]"

End If

'ADO
'Set cnn = CurrentProject.Connection
'Set rst = New ADODB.Recordset
'rst.Open sqlSort, cnn, adOpenStatic, adLockReadOnly, 1
'DAO
Set Cdb = CurrentDb()
Set rst = Cdb.OpenRecordset(sqlSort, dbOpenSnapshot)
rst.MoveLast

'How many observatons? For example, N=12
N = rst.RecordCount

'Which observation would, theoretically, _
be the pTH "true" percentile.
'e.g., for 25th percentile would be _
the 0.25*(12+1)=3.25th observation
break_pt = (p / 100) * (N + 1) '3.25 = (25/100)*(12+1)

'There's 2 special extreme cases we need to worry about!

'small sample for small percentile
If break_pt < 1 Then break_pt = 1
'small sample for large percentile
If break_pt > N Then break_pt = N

'But since there's no such thing as a _
3.25th observation, we estimate it _
'somewhere between the 3rd and 4th observations. _
It'll be approximately: p = r1*low_obs + r2*high_obs

low_obs = Int(break_pt) '3 = int(3.25)
high_obs = low_obs + 1 '4 = 3 + 1

'Now, we have to interprolate between the "boundaries"
r1 = high_obs - break_pt '0.75 = 4 - 3.25
r2 = 1 - r1 '0.25 = 1 - 0.75

'Since we have determined the needed _
observations and their weights we can move to the _
projected low observation and loop _
through the record set until we reach the _
high observation to calc the resulting percentile

'DAO, where absolution position is 0 based
rst.AbsolutePosition = low_obs - 1: recno = low_obs - 1
'ADO where absolution position is 1 based
'rst.AbsolutePosition = low_obs: recno = low_obs - 1
DoEvents

Do Until rst.EOF
recno = recno + 1
If recno = low_obs Then x = r1 * rst(0)
If recno = high_obs Then
x = x + r2 * rst(0)
Exit Do
End If
rst.MoveNext
Loop
'We now have our percentile!
Percentile = x

rst.Close
Set rst = Nothing
'DAO
Set Cdb = Nothing
'ADO
'Set Conn = Nothing

End Function

'Public Sub Test()
'Dim strMsg As String
' strMsg = "25th = " & Percentile("YourField", "YourTable", 25) _
' & vbNewLine & _
' "Median = " & Percentile("YourField", "YourTable", 50) _
' & vbNewLine & _
' "75th = " & Percentile("YourField", "YourField", 75)
'MsgBox strMsg
'End Sub

also I don't know how to use last function strWHERE- maybe is a solution of my problem


anyone?
 
Create a query that does the group by. Then create a second query based on the first query and use your function there.
 
I'm not sure if I understand you.
I have too much groups that I can make a query for the each group.
If I made a query with grouped data, I have to make 200 more queries and if I wanna chage something like date to filter, I have to change on every query that I create.
 
qry1:
Select fld1, fld2, Sum(fld3) As SumOfFld3
From yourtable
Group by fld1, fld2;

qry2:
Select fld1, fld2, YourFunction(SumOfFld3) as calcResult
From qry1;
 
Pat thx for your help,
if I understand good, idea is to made a 1st qry that calculate sum of grouped values
and than in second qry take out a percentile from field of sum,
that should be grouped by same fields
but it doesn't work because the code calculates percentile from the sum field for the whole qry 1
 
I think that this might be one of those situations where the first query has to create a table and the second query take in the table.

Brian
 
it's true what you said ,
but my problem is that I have to do to much queries (about 200) to make function that
 
Rather than making 200 queries/funtions, make one that takes a parameter.
 
I don't understand why you need 200 queries/functions.
qry1 will produce a result showing 200 groups and the sum for each of those groups, I have suggested that that might need to be a make table query, thus the table would show that.
qry2 would then produce the resulting percentile for each of those groups.

Brian
 
I'm little bit confused
I created first qry from a table which calculate a sum of the groups:

SELECT [table1].CODIGO, Sum([table2].CLOROFILA) AS SumaDeCLOROFILA
FROM [table1] INNER JOIN [table2] ON [table1].CODIGO = [table2].CODIGO
GROUP BY [table1].CODIGO;

than I created second qry where I use my perc.function

SELECT Consulta1.CODIGO, Percentile("SumaDeCLOROFILA","Consulta1",90) AS Expr2
FROM Consulta1;

and than I get just percentile calculated from the whole column of sums of groups

so maybe I don't understand what you want to say
but I appreciate it
code which I use calculate percentile for the whole column of the table/qry
and doesn't make groups like integrated sum function do
 
I hadn't looked at your code and have to admit to not really understanding it, I was really only replying to a problem on the 2 queries not working.
However I notice that your code reads the whole dataset, as the data is sorted cannot it be modified to work group by group?

Or am I way off course here.

Brian
 
You don't need to Sum the field in order to run the percentile function.

See the query in the attached database (and a new percentile function which returns the same percentile values as in Excel.)

The percentile values returned by your function are different. Maybe the underlying formulas used are not the same.
.
 

Attachments

Last edited:
finely it works!
It will save lots of hours at my work.
Thanks a lot for your help I really appreciate it:):):):):):):):)
 
Just eager to learn something new - to me.
Could someone tell me what "xxth percentile" is?

Many thanks!
 
Maybe the following example sentence from the Oxford Advanced Learner's Dictionary can show you what the word percentile means.

"Overall these students rank in the 21st percentile on the tests - that is, they did worse than 79 per cent of all children taking the test."
.
 

Users who are viewing this thread

Back
Top Bottom