Results 1 to 4 of 4
  1. #1
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62

    Multiple Field Search

    Hello all,



    I have a problem with a search whereby I have multiple fields that can optionally be filled in to search; these are Unbound textboxes and a couple combo boxes. They reference multiple fields. Here's an example form:

    This search has six fields; I want to be able to search on any of those fields:
    Click image for larger version. 

Name:	Search.PNG 
Views:	15 
Size:	10.8 KB 
ID:	31572

    These results are fine when all the fields are filled out, as I believe I can get my search results to give me what I want:
    Click image for larger version. 

Name:	ResultsA.PNG 
Views:	16 
Size:	7.8 KB 
ID:	31573

    The problem I'm having is when the field is skipped, having those 'spaces' in the search (highlighted).
    Click image for larger version. 

Name:	ResultsB.PNG 
Views:	15 
Size:	6.6 KB 
ID:	31574

    Here's the VBA I want to use for the search:
    Code:
    Private Sub btnSearch_Click()
        Dim strFilter As String
        Dim strSearch As String
        On Error Resume Next
        
        If Me.txtSearch.Text <> "" Then
            strSearch = "'*" & Replace(Me.txtSearch.Text, "'", "''") & "*'"
            strFilter = "[FirstName] Like " & strSearch & " OR [LastName] Like " & strSearch & " OR [SSN] Like " & strSearch _
                    & " OR [City] Like " & strSearch & " OR [State] Like " & strSearch & " OR [Country] Like " & strSearch
            Me.Filter = strFilter
            Me.FilterOn = True
        Else
            Me.Filter = ""
            Me.FilterOn = False
        End If
        
        If Me.Recordset.RecordCount = 0 Then
            Me.Filter = ""
            Me.FilterOn = False
            Me.txtSearch.SetFocus
            Me.txtSearch.Text = ""
            MsgBox "No results found."
            Exit Sub
        End If
    End Sub
    Does anyone know how to get this search working appropriately? Again, I want the results to work whether or not a field is filled in and to read that non-filled in value as null, rather than a " " identified in the text box for the search field.

    Thank you!

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Have a read and a download of Allen Brownes example search form here http://allenbrowne.com/ser-62.html
    It describes the techniques to use in a practical example.

  3. #3
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by Minty View Post
    Have a read and a download of Allen Brownes example search form here http://allenbrowne.com/ser-62.html
    It describes the techniques to use in a practical example.
    I will certainly read this over the weekend and come in to tackle this Monday. Thanks, much!

    By the way, I'm toying with the concatenated search bar by using a bunch of Iif statements with a trim function; will test both and get back. Thanks!

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Are you saying the extra spaces cause the filter to fail? I would not expect them to be an issue. However, consider:

    =[FirstName] + " " & [LastName] + " " & [SSN] + " " & [City] + " " & [State] + " " & [Country]
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-30-2016, 03:41 AM
  2. Replies: 8
    Last Post: 09-02-2015, 03:00 PM
  3. Replies: 4
    Last Post: 05-17-2013, 02:38 AM
  4. Replies: 1
    Last Post: 05-03-2012, 04:59 AM
  5. Create filter for multiple field search
    By luvsmel in forum Forms
    Replies: 5
    Last Post: 01-02-2012, 11:12 PM

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
  •  
Other Forums: Microsoft Office Forums