Search Button with Multiple Criteria

[injanib]

Hello all. I am new to this forum. I need help with the following.



On my form, I have two unbound text fields, a search button and a rest button.
The code on the search button is

Private Sub cmdSearch_Click()

Dim strFilter As String
If (Len(txtSearchID) & "") > 0 Then strFilter = "[InstructorID]='" &_ txtSearchID & "'"
If (Len(txtSearchName) & "") > 0 Then
If Len(strFilter) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "[InstructorName] Like '*" & txtSearchName & "*'"
Me.Filter = strFilter
Me.FilterOn = True
Else
MsgBox "No records found.", vbInformation + vbOKOnly, "No Record Found!"
End If
End Sub

My reset button just sets the me.filteron=false

This works fine except that when I first open the form and hit the search
button with nothing in the unbound fields, it applies the filter and shows a
blank record. Only after I reset once, then it starts popping up the message
saying no records was found.

Does anyone know why this happens?

Also, how can I modify this code to make it work with three and four criteria fields?
Thanks in advance

[NTC]

well 2 things jump out; maybe one is close. I don't see you clearing the filter - and so the prior filter may be in there when you reopen the form.

the other thing is that if the fields are blank, and therefore null.... then
If (Len(txtSearchID) & "") > 0
If (Len(txtSearchName) & "") > 0
neither of these two would be >0 so the IF statement isn't triggered...

not sure - but hopefully this helps get you in the right direction.

[injanib]

Thank you for the suggestion.
I checked for the first case and that wasn't it. I reset the filter before closing the form, then I restarted the form and tried it and saw the same behavior as before.

I do not know how to change the code to check for the second case, but I think that that is what is causing the issue. I tried the following.

Dim strFilter As String
If (Len(txtSearchID) & "") > 0 And Not IsNull(Me.txtSearchID) Then strFilter = "[InstructorID]='" &_ txtSearchID & "'"
If (Len(txtSearchName) & "") > 0 And Not IsNull(Me.txtSearchName) Then
If Len(strFilter) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "[InstructorName] Like '*" & txtSearchName & "*'"
Me.Filter = strFilter
Me.FilterOn = True
Else
MsgBox "No records found.", vbInformation + vbOKOnly, "No Record Found!"
End If

Unlike before, this does pop up the message saying no record found if the fields are null even when I first open the form. However, the filter does not work. If I enter a value in the txtSearchID only and search, it doesn't find any record although the record exists. I will have to enter value in both of the fields to find the record.
I do not have to enter a value in the txtSearchID if I want to search record for txtSearchName. It still finds the records.

Basically, it only works properly when i search records for txtsearchName.

any additional input will be greatly appreciated.