Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Jan 23, 2025
Solved

Filter Form with Like and Or

 

Hello Experts, 

I am having a tough time adding a condition to a filter on a form. 

It works with 1 condition but not sure about the OR part (sq, dq)

Maybe it is correct.

The error I get is "Type Mismatch" see pic below.

The row highlighted is the Remarks line. 

It seems odd because the fields are "short text" in the underlying table. 

Do you see an issue with the OR statement?  

If not, any ideas why I would be getting that error knowing that the fields are text?  

Private Sub txtEndUser_AfterUpdate()

       'http://allenbrowne.com/ser-28.html
    
      If IsNull(Me.txtEndUser) Then
        Me.FilterOn = False
      Else

        Me.Filter = "[Remarks 1] Like '*" & [txtEndUser] & "*'" Or "[Remarks 2] Like '*" & [txtEndUser] & "*'"
        Me.FilterOn = True
      End If
      
End Sub

here is the table showing they are short text. 

the table is imported. 

as a reminder, I have no issues with 1 condition meaning it filters fine. 

 

access
  • Kidd_Ip's avatar
    Kidd_Ip
    Jan 24, 2025

    Try this:

     

    Private Sub txtEndUser_AfterUpdate()

    ' http://allenbrowne.com/ser-28.html

    If IsNull(Me.txtEndUser) Then
        Me.FilterOn = False
    Else
        Me.Filter = "[Remarks 1] Like '*" & Me.txtEndUser & "*' OR [Remarks 2] Like '*" & Me.txtEndUser & "*'"
        Me.FilterOn = True
    End If

End Sub

     

5 Replies

Sort By
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Jan 24, 2025

    Have you tried using parentheses to manage the two arguments?

     Me.Filter = ("[Remarks 1] Like '*" & [txtEndUser] & "*'") Or ("[Remarks 2] Like '*" & [txtEndUser] & "*'")

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jan 24, 2025

    Try this:

     

    Private Sub txtEndUser_AfterUpdate()

    ' http://allenbrowne.com/ser-28.html

    If IsNull(Me.txtEndUser) Then
        Me.FilterOn = False
    Else
        Me.Filter = "[Remarks 1] Like '*" & Me.txtEndUser & "*' OR [Remarks 2] Like '*" & Me.txtEndUser & "*'"
        Me.FilterOn = True
    End If

End Sub

     

    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Jan 24, 2025

      perfect Kidd!  Its odd that I need to use Me. since I leave that out of other queries (single criteria) but I assume you need the Me when its a multiple criteria query.  I confirm it does work.  

       

      thank you very much!  

      • Karl_Donaubauer's avatar
        Karl_Donaubauer
        MVP
        Jan 25, 2025

        Hi,

        Me is good practice, but only really necessary if there would otherwise be name conflicts with variables etc. Your mistake and the crucial thing about the working variant is rather that the OR operator must be integrated into the string, i.e. it must be inside the quotation marks, which it was not in your variant.

        Servus
        Karl
        ****************
        Access Forever, News, DevCon
        Access-Entwickler-Konferenz AEK

Resources