Results 1 to 6 of 6

How can filter multiple fields in a search (text box)

  1. #1
    Coderama is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    5

    How can filter multiple fields in a search (text box)

    Hi guys! I am so stuck on VBA today, i might as well pop the question.
    Hopefully someone can help me cause im lost.
    It's about

    strFilter = "orderstatus Like '*" & _.

    Orderstatus is the field im searching in (filtering down).
    But i would like some other fields in there. Lets call it field22.

    strFilter = "orderstatus And field22 Like '*" & _. ???? I tried many things but nothing works


    Heres the full code:

    Private Sub txtFilter_AfterUpdate() Dim strFilter As String

    If Len(Trim(Me.txtfilter.Value & vbNullString)) > 0 Then
    strFilter = "orderstatus Like '*" & _
    Replace(Me.txtfilter.Value, "'", "''") & _
    "*'"
    Me.Filter = strFilter
    Me.FilterOn = True

    Else
    Me.FilterOn = False
    End If


    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,754
    strFilter = "orderstatus Like '*" & txtBox & "*'"

    You can't use REPLACE in a filter.
    that goes in a query.

  3. #3
    Coderama is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    5
    So i put it like this?

    Private Sub txtFilter_AfterUpdate() Dim strFilter As String

    If Len(Trim(Me.txtfilter.Value & vbNullString)) > 0 Then
    strFilter = "orderstatus Like '*" & txtBox & "*'"
    Me.Filter = strFilter
    Me.FilterOn = True

    Else
    Me.FilterOn = False
    End If
    End Sub

    I dont really understand. I can't add more fields to search? Only orderstatus?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,486
    strFilter = "orderstatus And field22 Like '*" & _. ???? I tried many things but nothing works
    You MUST explicitly search fields.

    Code:
    Private Sub txtFilter_AfterUpdate() Dim strFilter As String
        Dim strFilter as String
    
       If Len(Trim(Me.txtfilter.Value & vbNullString)) > 0 Then
          strFilter = "orderstatus Like '*" & txtBox & "*'"
           strFilter =  strFilter & " AND field22 = " & Me.SomeControl   'if field22 is a number
    
           Me.Filter = strFilter
           Me.FilterOn = True
    
       Else
           Me.Filter = ""
           Me.FilterOn = False
       End If
    
    End Sub
    Note: there needs to be a lot more checking in the code.
    See Allen Browne's example at http://allenbrowne.com/ser-62code.html
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,353
    I don't know how many tables are involved in your "filter" issue.
    You could look at the Universal search here and see if it might give you the result you're looking for.

    Good luck.

  6. #6
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    178
    You may create a Query with a new field combining data of both fields and use a simple search term. Check this out for a sample trial run: http://www.msaccesstips.com/2009/12/text-search-filter-web-style/

    http://www.msaccesstips.com (Learn MS-Access Tips and Tricks)
    Learn Advanced MS-Access Programming with examples and VBA.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 02-26-2014, 05:06 PM
  2. Replies: 1
    Last Post: 03-06-2013, 05:51 AM
  3. Search multiple fields from one text box
    By tommy93 in forum Queries
    Replies: 21
    Last Post: 01-10-2012, 02:18 AM
  4. Create filter for multiple field search
    By luvsmel in forum Forms
    Replies: 5
    Last Post: 01-02-2012, 11:12 PM
  5. Replies: 4
    Last Post: 09-22-2010, 01:47 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums