Hi all,
I am trying to calculate percentile values by creating a VBA code:
Option Compare Database
Public Function DPercentileExcel(expr As String, domain As String, Percentile As Double) As Variant
Dim strSQL As String
Dim N As Integer
Dim nSubk As Double
Dim vSubk As Variant
Dim vSubkPlus1 As Variant
Dim rs As DAO.Recordset
strSQL = "SELECT " & expr & " FROM " & domain
strSQL = strSQL & " WHERE NOT " & expr & " IS NULL ORDER BY " & expr
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If Not (rs.EOF And rs.BOF) Then
rs.MoveLast
rs.MoveFirst
Else
Exit Function
End If
N = rs.RecordCount
nSubk = Percentile / 100 * (N - 1) + 1
'using NIST nSubK = Percentile / 100 * (N+1)
If nSubk = 1 Then
DPercentileExcel = rs.Fields(expr)
ElseIf nSubk = N Then
rs.MoveLast
DPercentileExcel = rs.Fields(expr)
Else
rs.AbsolutePosition = nSubk - 1 '0 based
vSubk = rs.Fields(expr)
rs.AbsolutePosition = nSubk '0 based
vSubkPlus1 = rs.Fields(expr)
'Debug.Print nSubk
'Debug.Print vSubk
'Debug.Print vSubkPlus1
DPercentileExcel = vSubk + (nSubk - Int(nSubk)) * (vSubkPlus1 - vSubk)
End If
End Function
Public Sub testPercentileExcel()
Debug.Print DPercentileExcel("operatingcostspersf", "step1_operatingcostspersf", 0)
End Sub
When I try to use this function in my query, it says that DPercentileExcel is an undefined function in the expression...
My SQL looks like this:
SELECT Percentiles.percentile, DPercentileExcel("operatingcostsperSF","step1_operatingcostspersf",[percentile]) AS percentileoperatingcostspersf
FROM Percentiles;
Please Help!
O and btw when I try to save the module name from the 'Module 5' that it automatically gave to a new name, it says file not found. Does this mean anything??
I am trying to calculate percentile values by creating a VBA code:
Option Compare Database
Public Function DPercentileExcel(expr As String, domain As String, Percentile As Double) As Variant
Dim strSQL As String
Dim N As Integer
Dim nSubk As Double
Dim vSubk As Variant
Dim vSubkPlus1 As Variant
Dim rs As DAO.Recordset
strSQL = "SELECT " & expr & " FROM " & domain
strSQL = strSQL & " WHERE NOT " & expr & " IS NULL ORDER BY " & expr
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If Not (rs.EOF And rs.BOF) Then
rs.MoveLast
rs.MoveFirst
Else
Exit Function
End If
N = rs.RecordCount
nSubk = Percentile / 100 * (N - 1) + 1
'using NIST nSubK = Percentile / 100 * (N+1)
If nSubk = 1 Then
DPercentileExcel = rs.Fields(expr)
ElseIf nSubk = N Then
rs.MoveLast
DPercentileExcel = rs.Fields(expr)
Else
rs.AbsolutePosition = nSubk - 1 '0 based
vSubk = rs.Fields(expr)
rs.AbsolutePosition = nSubk '0 based
vSubkPlus1 = rs.Fields(expr)
'Debug.Print nSubk
'Debug.Print vSubk
'Debug.Print vSubkPlus1
DPercentileExcel = vSubk + (nSubk - Int(nSubk)) * (vSubkPlus1 - vSubk)
End If
End Function
Public Sub testPercentileExcel()
Debug.Print DPercentileExcel("operatingcostspersf", "step1_operatingcostspersf", 0)
End Sub
When I try to use this function in my query, it says that DPercentileExcel is an undefined function in the expression...
My SQL looks like this:
SELECT Percentiles.percentile, DPercentileExcel("operatingcostsperSF","step1_operatingcostspersf",[percentile]) AS percentileoperatingcostspersf
FROM Percentiles;
Please Help!
O and btw when I try to save the module name from the 'Module 5' that it automatically gave to a new name, it says file not found. Does this mean anything??
