Hello,
I am a beginner in using modules and functions in Access and I have tried to find a solution for this problem without success.
I have tried to use a Percentile Function and call it from a query but I always get this error:
Undefined function <name> in expression. (Error 3085)
The Function that I took from the internet is as follows:
-----------------------------------
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
-------------------------------
The module with the fucntion is correctly present in the database.
I used the Builder tool and the query SQL view shows the following:
-----------------------
SELECT Percentile([AlexaRank],[Table1],0.25,[«strWhere»]) AS Expr1
FROM Table1;
-----------------------
I tried with several other functions but I always get the same error. The only exception is when I use the built-in functions already available. They work with no problem.
Can anybody please direct me in the right track? Please consider that I am a beginner.
Cheers,
FF
I am a beginner in using modules and functions in Access and I have tried to find a solution for this problem without success.
I have tried to use a Percentile Function and call it from a query but I always get this error:
Undefined function <name> in expression. (Error 3085)
The Function that I took from the internet is as follows:
-----------------------------------
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
-------------------------------
The module with the fucntion is correctly present in the database.
I used the Builder tool and the query SQL view shows the following:
-----------------------
SELECT Percentile([AlexaRank],[Table1],0.25,[«strWhere»]) AS Expr1
FROM Table1;
-----------------------
I tried with several other functions but I always get the same error. The only exception is when I use the built-in functions already available. They work with no problem.
Can anybody please direct me in the right track? Please consider that I am a beginner.
Cheers,
FF
