Hi, I am struggling with what may be a simple issue.
I am using a function to calculate the quartiles from an existing query and enter the corresponding EqNum and EDescription into a table.
When I run the code below it asks me for a value for SysAssetCritRankQ from the rstPercentileRST parameters.
I have tried adding "" and [] but they aren't working. Where am I going wrong? (The issue with self taught SQL/VBA)
SysAssetCritQ = Query Name
Total = Calculated field in query
PercentileRST function
I am using a function to calculate the quartiles from an existing query and enter the corresponding EqNum and EDescription into a table.
When I run the code below it asks me for a value for SysAssetCritRankQ from the rstPercentileRST parameters.
I have tried adding "" and [] but they aren't working. Where am I going wrong? (The issue with self taught SQL/VBA)
SysAssetCritQ = Query Name
Total = Calculated field in query
Code:
strSQL = "SELECT SysAssetCritRankQ.EqNum, SysAssetCritRankQ.EDescription INTO [EOQComboT]FROM SysAssetCritRankQ " & _
"WHERE Total >= PercentileRst([SysAssetCritRankQ], Total, 0.75); "
PercentileRST function
Code:
Public Function PercentileRst(RstName As String, fldName As String, PercentileValue As Double) As Double
'This function will calculate the percentile of a recordset.
'The field must be a number value and the percentile has to
'be between 0 and 1.
If PercentileValue < 0 Or PercentileValue > 1 Then
MsgBox "Percentile must be between 0 and 1", vbOKOnly
End If
Dim PercentileTemp As Double
Dim dbs As Database
Set dbs = CurrentDb
Dim xVal As Double
Dim iRec As Long
Dim i As Long
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
RstOrig.Sort = fldName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
RstSorted.MoveLast
RstSorted.MoveFirst
xVal = ((RstSorted.RecordCount - 1) * PercentileValue) + 1
'x now contains the record number we are looking for.
'Note x may not be whole number
iRec = Int(xVal)
xVal = xVal - iRec
'i now contains first record to look at and
'x contains diff to next record
RstSorted.Move iRec - 1
PercentileTemp = RstSorted(fldName)
If xVal > 0 Then
RstSorted.MoveNext
PercentileTemp = ((RstSorted(fldName) - PercentileTemp) * xVal) + PercentileTemp
End If
RstSorted.Close
RstOrig.Close
Set RstSorted = Nothing
Set RstOrig = Nothing
Set dbs = Nothing
PercentileRst = PercentileTemp
End Function