Percentile and Parameter query (1 Viewer)

Tlandry

New member
Local time
Today, 11:17
Joined
Dec 10, 2017
Messages
3
Would anyone happen to know how to modify this VBA code to take into account an underlying date between parameter in the waittime query it will be referencing?
[ENTER START DATE mm/dd/yyyy:]"[ENTER END DATE mm/dd/yyyy:]",

Code:
Public Function PercentileRst(RstName As String, DAYS_DFF 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 = DAYS_DFF
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(DAYS_DFF)
If xVal > 0 Then
RstSorted.MoveNext
PercentileTemp = ((RstSorted(DAYS_DFF) - PercentileTemp) * xVal) + PercentileTemp
End If
RstSorted.Close
RstOrig.Close
Set RstSorted = Nothing
Set RstOrig = Nothing
Set dbs = Nothing
PercentileRst = PercentileTemp
End Function

Private Sub test()
MsgBox PercentileRst("waittime", "DAYS_DFF", 0.9)
End Sub
 
Last edited by a moderator:

isladogs

MVP / VIP
Local time
Today, 18:17
Joined
Jan 14, 2017
Messages
18,186
Not sure why this got overlooked but hopefully I can give this a bump

Why can't you just include the date range in the criteria for the underlying query used for the recordset?
 

Users who are viewing this thread

Top Bottom