search for records based on multiple criteria from multipe search boxes

heathxp

Registered User.
Local time
Today, 10:32
Joined
Jun 27, 2019
Messages
27
I am trying to create a form that allows you to return results based on multiple criteria.

I have FirstName field, LastName field, and State Field.

I also have text boxes named searchFirst, searchLast, searchState where users can input criteria.

How do I build the filter so that it takes the criteria from any of these 3 fields?

For example. If I type in First Name "Mike" and State "New York" I want to see all Mikes from New York.


If I use the code below the form filters based on the first criteria (First Name) but ignores anything I type into the "State" text box.

Here's the code from one of the search boxes keyup event

Code:
Private Sub txt_searchFirst_KeyUp(KeyCode As Integer, Shift As Integer)
    On Error GoTo ErrHandler
    Dim filterTextsearchFirst As String
    Dim filterTextsearchLast As String
    Dim filterTextsearchState As String
    'Apply or update filter based on user input.
    Me.searchFirst.SetFocus
    filterTextsearchFirst = searchFirst.Text & ""
    filterTextsearchLast = searchLast.Text & ""
    filterTextsearchState = searchState.Text & ""
    If Len(filterTextsearchFirst) > 0 Or Len(filterTextsearchLast) > 0 Or Len(filterTextsearchState) > 0 Then
   Me.Form.Filter = "[FirstName] LIKE '*" & filterTextsearchFirst & "*' AND [LastName] LIKE '*" & filterTextsearchLast & "*' AND [STATE] LIKE '*" & filterTextsearchState & "*'"
        Me.FilterOn = True
        'Retain filter text in search box after refresh.
        With searchFirst
            .SetFocus
            .Value = filterTextsearchFirst
            .SelLength = 0
            .SelStart = Len(searchFirst.Text)
        End With

    Else
        ' Remove filter.
        Me.FilterOn = False
        Me.Filter = ""
        searchFirst.SetFocus
    End If
    Exit Sub
ErrHandler:
    Resume Next

End Sub
 
Last edited:
Hi. The way I do it is build the criteria one at a time. For example:
Code:
strCriteria = " 1=1 "


If Me.ControlName1 > "" Then
    'build first criteria
    strCriteria = strCriteria & " AND Field1=" & Me.ControlName1
End If


If Me.ControlName2 > "" Then
    'add second criteria
    strCriteria = strCriteria & " AND Field2=" & Me.ControlName2
End If


...and so on...
 
Hi. The way I do it is build the criteria one at a time.


I did just that and it's still the same issue?

Code:
Private Sub txt_searchFirst_KeyUp(KeyCode As Integer, Shift As Integer)
    On Error GoTo ErrHandler
    Dim filterTextsearchFirst As String
    Dim filterTextsearchLast As String
    Dim filterTextsearchState As String
    Dim strCriteria As String
    'Apply or update filter based on user input.
    Me.searchFirst.SetFocus
    filterTextsearchFirst = searchFirst.Text & ""
    filterTextsearchLast = searchLast.Text & ""
    filterTextsearchState = searchState.Text & ""
    If Len(filterTextsearchFirst) > 0 Then
    strCriteria = "[FirstName] LIKE '*" & filterTextsearchFirst & "*'"
    If Len(filterTextsearchLast) > 0 Then
    strCriteria = strCriteria & " AND [LastName] LIKE '*" & filterTextsearchLast & "*'"
    End If
    If Len(filterTextsearchState) > 0 Then
    strCriteria = strCriteria & " AND [State] LIKE '*" & filterTextsearchState & "*'"
    End If
        Me.Form.Filter = strCriteria
        Me.FilterOn = True
        'Retain filter text in search box after refresh.
        With searchFirst
            .SetFocus
            .Value = filterTextsearchFirst
            .SelLength = 0
            .SelStart = Len(searchFirst.Text)
        End With

    Else
        ' Remove filter.
        Me.FilterOn = False
        Me.Filter = ""
        searchFirst.SetFocus
    End If
    Exit Sub
ErrHandler:
    Resume Next

End Sub

If I debug the strCriteria, It always returns serchLast and searchState as 0 length. No sure why but I'm sure this is the actual issue
 
Last edited:
I did just that and it's still the same issue?
...
If I debug the strCriteria, It always returns serchLast and searchState as 0 length. No sure why but I'm sure this is the actual issue
Then, you might have to "step through" your code to see why the criteria is coming up empty.
 
Then, you might have to "step through" your code to see why the criteria is coming up empty.

Thanks. Ok so I think I figured it out but I'm not sure why it's acting like this.

The issue is here:

Code:
    filterTextsearchFirst = searchFirst.Text & ""
    filterTextsearchLast = searchLast.Text & ""
    filterTextsearchState = searchState.Text & ""

If I change it to :


Code:
    filterTextsearchFirst = searchFirst.Text & ""
    filterTextsearchLast = searchLast.Value & ""
    filterTextsearchState = searchState.Value & ""

The first search box were I type must be .Text, the rest of the searchboxes must be .Value.

Any reason for this?
 
Thanks. Ok so I think I figured it out but I'm not sure why it's acting like this.

The issue is here:

Code:
    filterTextsearchFirst = searchFirst.Text & ""
    filterTextsearchLast = searchLast.Text & ""
    filterTextsearchState = searchState.Text & ""
If I change it to :


Code:
    filterTextsearchFirst = searchFirst.Text & ""
    filterTextsearchLast = searchLast.Value & ""
    filterTextsearchState = searchState.Value & ""
The first search box were I type must be .Text, the rest of the searchboxes must be .Value.

Any reason for this?
Oh, I see. You should be able to just Value for all of them. Or even yet, don't use any, since Value is the default anyway. Probably the reason for the original problem is because you can only use the Text property if the control has the focus; otherwise, I would expect you to get an error.
 
Oh, I see. You should be able to just Value for all of them. Or even yet, don't use any, since Value is the default anyway. Probably the reason for the original problem is because you can only use the Text property if the control has the focus; otherwise, I would expect you to get an error.

Thanks for the explanation.

If I don't use .Text for the first one, the setFocus doesn't work correctly.
 
Thanks for the explanation.

If I don't use .Text for the first one, the setFocus doesn't work correctly.
Hi. That's probably a separate issue. Or, perhaps you may not need to set the focus there at all, as long as you don't use Text afterwards. Try using Value, or like I said earlier, none at all and just rely on the default.
 

Users who are viewing this thread

Back
Top Bottom