Access How to use Me.Filter cmd

Question

I have a question dealing with a filter function.

I am fine when using a button to search if there is any related data when I entered a keyword. And my code like,

Private Sub Command112_Click()
    Dim strSearch As String
    Dim strFilter As String

    strSearch = "'*" & Forms![TestForm].SearchInput & "*'"
    Debug.Print strSearch

    strFilter = _
    "[IMSDP] Like " & strSearch & _
                " Or [EN8] Like " & strSearch & _
                " Or [EN10] Like " & strSearch & _
                " Or [Card] Like " & strSearch & _
                " Or [Status] Like " & strSearch & _
                " Or [IMSDP] Like " & strSearch

    Debug.Print strFilter

    Me.Filter = strFilter
    Me.FilterOn = True
End Sub

But once I want to search more than one keyword, it doesn't work. And my code like following,

Private Sub Search_Click()

    Dim strSearch As Variant
    Dim strFilter1 As Variant
    Dim strFilter2 As Variant
    Dim SpacePosition As Variant
    Dim Lstr As Variant
    Dim Rstr As Variant
    Dim IMSDP1 As Variant
    Dim IMSDP2 As Variant

    strSearch = "'*" & Forms![tryForm].IMSDPInput & "*'"
    Debug.Print strSearch

    SpacePosition = InStr(1, [strSearch], " ")
    Lstr = Trim(Left([strSearch], [SpacePosition] - 1))
    Rstr = Trim(Right([strSearch], Len([strSearch]) - [SpacePosition]))

    IMSDP1 = Lstr
    IMSDP2 = Rstr

    MsgBox "IMSDP1 is " & IMSDP1 & " and IMSDP2 is " & IMSDP2 & ""

    strFilter1 = _
    "[IMSDP] Like " & IMSDP1 & _
    "And [IMSDP] Like " & IMSDP2

    Me.Filter = strFilter
    Me.FilterOn = True

End Sub

Can anyone help? Thank you.

I am now having the code like follow, (you may think Status = IMSDP)

Private Sub Command14_Click()
Dim Status_Filter As Variant
Dim Status_Input As Variant
Dim SpacePosition As Variant
Dim Status1 As Variant
Dim Status2 As Variant

Status_Input = "'*" & Forms![tryForm].StatusInput & "*'"
SpacePosition = InStr(1, [Status_Input], " ")

If (SpacePosition = 0) Then
    Status_Filter = _
      "[Status] Like " & Status_Input

Else
    Status1 = Left([Status_Input], [SpacePosition] - 1)
    Status2 = Right([Status_Input], Len([Status_Input]) - [SpacePosition])

    MsgBox "Status1 is " & Status1 & " and Status2 is " & Status2 & ""

    Status_Filter = _
       "[Status] Like " & Status1 & _
       "Or [Status] Like " & Status2

End If

Debug.Print Status_Filter
Me.Filter = Status_Filter
Me.FilterOn = True
End Sub

It works if I only enter one keyword(e.g. "20") to search but fail if I entering something like "20 27".

I only know I can write in Excel like, Range("A1").AutoFilter Field:=5, Criteria1:=Status1, Operator:=xlOr, Criteria2:=Status2 But I have no idea how to do it in Access

Answer 1

If Status field is number type and users always enter 'keywords' separated by a space, consider the following:

Private Sub Command14_Click()
If Not IsNull(Me.StatusInput) Then
   Me.Filter = "IN(" & Replace(Me.StatusInput, " ", ",") & ")"
End If
Me.FilterOn = True
End Sub

If they enter separated with comma, even simpler: Me.Filter = "IN(" Me.StatusInput & ")"

For a text field:
"IN('" & Replace(Me.StatusInput, " ", "','") & "')"

"IN('" & Replace(Me.StatusInput, ",", "','") & "')"

Regardless of code structure, depends on users entering string in textbox correctly and consistently.

If you want more control over the values user can enter, use a multi-select listbox and VBA loops through the listbox selected items to construct the comma separated values criteria. A fairly common topic and many code examples available for looping listbox.

And no array needed after all!