Question on applying Modules funtion

[tttt2222]

I have a table as the follwoing:
RI Region Time
6.6 2 2006-01-23
8.1 1 2006-01-03
5.1 2 2006-01-03
3.4 2 2006-01-07
7.2 2 2006-01-07
3.5 2 2006-01-09
6.2 3 2006-01-10
6.1 2 2006-01-09
11.0 2 2006-01-18
4.3 6 2006-01-18
5.4 1 2006-01-05
11.3 2 2006-01-19

I have a function to calculate the percentile of the column RI. If I donot control the Time in my Query, it works well.
But if I add:
Where(Case.Time)>=[Forms]![frmWitness]![txtStartDate] And (Case.Time)<=[Forms]![frmWitness]![txtEndDate]
it doesnot work.
Any one can help me? Many many Thanks.




The following function is from internet:



Public Function calpercentile(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
calpercentile = -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)
'st.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!
calpercentile = x

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

End Function

 

[simon551]

is there an error message? I'm not an expert in this at all, but I might suggest trying to put the parameters in a query and then reference the query rather than using the sql in the code.

 

[Eupher]

Hiya, tttt2222,

You must delimit date/time values with the pound sign, and properly concatenate your SQL string, i.e.:

"Where (Case.Time) >= #" & [Forms]![frmWitness]![txtStartDate] _
& "# And (Case.Time) <= #" & [Forms]![frmWitness]![txtEndDate] & "#"

And in this case, looks like you could use the Between keyword:

"Where (Case.Time) [highlight]Between[/highlight] #" & [Forms]![frmWitness]![txtStartDate] _
& "# And #" & [Forms]![frmWitness]![txtEndDate] & "#"

HTH,

Ken S.

 

[tttt2222]

Thanks a lot, but it still doesenot work.

 

[Golom]

What "does not work" exactly? The SQL? The function? Does it raise an error? Give the wrong answers?

I would

'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

    [red]Debug.Print sqlSort[/red]

and see exactly what the sql looks like that you are processing.

 

[tttt2222]

The function works well, I can obtain the right result, but if I call this function in query with:

Where(Case.Time)>=[Forms]![frmWitness]![txtStartDate] And (Case.Time)<=[Forms]![frmWitness]![txtEndDate]

It doesnot work.

If withou "Where..." ,it works.

 

[Golom]

Again ... what does "does not work" mean?

Is it raising an error? Producing incorrect results? Hanging up?

 

[SkipVought]

Hi,

You seem to be IGNORING several previous responses to your question.

If Case.Time is a DATE data type and your are entering a STRING in txtStartDate & txtEndDate, then your query will not work as you cannot compare a DATE to a STRING.

Convert your STRINGS to DATES, using the # Delimiters as Ken S previously suggested.

If that's not the issue, then answer Golom's questions CLEARLY, COMPLETELY & CONCISELY.




Skip,

_[red][/red]

 

[PHV]

Get rid of the Where keyword as it is added by the function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

or set a [blue]break point[/blue] on the hilited line below and add the [blue]Debug.Print sqlSort[/blue] line.

[blue]    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
    [b]Debug.Print sqlSort[/b]
    
    'ADO
    [COLOR=white red]Set cnn = CurrentProject.Connection[/color][/blue]

View the results in the [blue]immediate window[/blue]. Any problems should be apparent . . .

See Ya! . . . . . .