Filter Listbox Question “Like” v. “>=”

[Accel45]

I am using the code below to filter a listbox. It works to filter the listbox. However I have a field “DaysSinceSent” where Like '*" & Me.txtDaysSinceSent & "*' does not accomplish what I need. “DaysSinceSent” is a number field and I want to return those records that exceed what is entered in “txtDaysSinceSent”. For example all records where “DaysSinceSent” is more that 90.

How do I modify this piece of code to filter on >= txtDaysSinceSent instead of Like?

If Not IsNull(Me.txtDaysSinceSent) Then
trWhere = strWhere & " (qryCaseLog.DaysSinceSent) Like '*" & Me.txtDaysSinceSent & "*' AND"
End If

Code Below is what I am working with. I removed a section to save looking through code that is functioning:

Private Sub Search_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT qryCaseLog.ItemID, qryCaseLog.DateSentToAAG, qryCaseLog.RName,qryCaseLog.DaysSinceSent " & _
"FROM qryCaseLog"

strWhere = "WHERE"

'strOrder = "ORDER BY qryCaseLog.ItemID;"
strOrder = "ORDER BY qryCaseLog.RLastName , qryCaseLog.RFirstName" & " DESC"

'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtCommitteeDecision) Then '<--If the textbox CommitteeDecision contains no data THEN do nothing
strWhere = strWhere & " (qryCaseLog.CommitteeDecision) Like '*" & Me.txtCommitteeDecision & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
<<<<<Section of Code Removed>>>>>

(The section below is what I am having issue with)
If Not IsNull(Me.txtDaysSinceSent) Then
trWhere = strWhere & " (qryCaseLog.DaysSinceSent) Like '*" & Me.txtDaysSinceSent & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.List68.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub

Thanks
Accel45

 

[PHV]

Have you tried this ?
strWhere = strWhere & " (qryCaseLog.DaysSinceSent) >= " & Me.txtDaysSinceSent & " AND"


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

 

[Andrzejek]

Also,

If Not IsNull(Me.txtDaysSinceSent[red].Text[/red]) Then

text boxes (usually) can not handle NULL, so I would suggest:

If Len(Trim(Me.txtDaysSinceSent.Text)) > 0 Then



Have fun.

---- Andy

 

[Accel45]

Thank you Andrzejek, I tried your suggestions, no luck, no records returned.

 

[Andrzejek]

Mine was just a suggestion, but I would look at PHV's code closer.

If you have:
strWhere = strWhere & " (qryCaseLog.DaysSinceSent) >= " & Me.txtDaysSinceSent & " AND"

and Me.txtDaysSinceSent.Text = 90, your SQL translates to:

strWhere = strWhere & " (qryCaseLog.DaysSinceSent) >= 90 AND"

And that should give you the right results.




Have fun.

---- Andy