Results 1 to 13 of 13

Search Multiple Data Using Search Button in a Form

  1. #1
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48

    Search Multiple Data Using Search Button in a Form

    Hi! I had a code that can search a data just using keyword like 123 it will show me the record that has 123 like "31231", "32123", etc. but I want to search multiple record which can be separated by comma like [123, 456] and the result will show me the record that is..



    31231
    32123
    32456
    24561

    LEGEND
    GOLDNEWCONNECT = Database/Table
    GOLDNEWCONNECT_ValidateSubform = Subform on my main form
    GOLDNEWCONNECT_ValidateBatch = The name of the main form
    Text30 = Textbox where SR Number will be putted
    Text34 = Textbox where Emboss Name will be putted

    I had a code like this.

    This will search the SR Number, which I change the code in the BuildFilter because I want to search multiple record which is separated by Comma which has an error.

    BUTTON CODE FOR SR NUMBER
    Code:
    Private Sub Command33_Click()
     'Update the record source
        Me.GOLDNEWCONNECT_ValidateSubform.Form.RecordSource = "SELECT * FROM GOLDNEWCONNECT " & BuildFilter
            
        ' Requery the subform
        Me.GOLDNEWCONNECT_ValidateSubform.Requery
    End Sub
    and this will search for the Emboss Name which I didn't change the code. For you also to see the search Like code, It's running but it doesn't search in multiple, just one at a time.

    BUTTON CODE FOR EMBOSS NAME
    Code:
    Private Sub Command36_Click()
    'Update the record source
        Me.GOLDNEWCONNECT_ValidateSubform.Form.RecordSource = "SELECT * FROM GOLDNEWCONNECT " & BuildFilter
            
        ' Requery the subform
        Me.GOLDNEWCONNECT_ValidateSubform.Requery
    End Sub
    and this is the BuildFilter code.
    Code:
    Private Function BuildFilter() As Variant
        Dim varWhere As Variant
        
        varWhere = Null  ' Main filter
     
        ' Check for LIKE First Name
        If Me.Text30 > "" Then
            varWhere = varWhere & "[SR_Number] LIKE In('*" & Me.Text30 & "*')"
        End If
            
       If Me.Text34 > "" Then
            varWhere = varWhere & "[Emboss_Name] LIKE ""*" & Me.Text34 & "*"" AND "
        End If
        
        ' Check if there is a filter to return...
        If IsNull(varWhere) Then
            varWhere = ""
        Else
            varWhere = "WHERE " & varWhere
        
            ' strip off last "AND" in the filter
            If Right(varWhere, 5) = " AND " Then
               varWhere = Left(varWhere, Len(varWhere) - 5)
            End If
        End If
        
        BuildFilter = varWhere
        
    End Function
    as you can see. I just put In() on the code because I read that I can you IN() so I can search using/separating comma.

    I don't know if I'm doing this right but please help me. This one will be pass tomorrow

    Thank you in advanced..

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,578
    The IN operator acting on the string from textbox won't work because it is a single string that contains comma characters, not multiple strings separated by commas. Example:

    This query works:
    SELECT FAAID FROM Airports WHERE FAAID IN("ANC", "FAI");

    This query does not work:
    SELECT FAAID FROM Airports WHERE FAAID IN("ANC,FAI");

    Throw the LIKE operator in and query errors. Each operator requires its own criteria.
    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.

  3. #3
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    But what if I don't use query? The subform is just getting some fields on the table and not getting a query. I'm super confused if I did this right or wrong? or what is wrong with it?

    Is the error is in this code?
    Code:
     varWhere = varWhere & "[SR_Number] LIKE In('*" & Me.Text30 & "*')"

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,578
    The function is building a string for the WHERE clause and passing that string to the calling procedure which then completes the SQL statement. Doesn't matter if the SQL is RecordSource, RowSource, Access query object, VBA recordset - the syntax rules are the same. As explained that syntax will not work.

    Textbox entry of <123, 456> is one string of characters, one piece of data, not a set of two distinct values. The IN operator requires a set of distinct values. Your textbox produces a set of 1 element.
    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.

  5. #5
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    But I have this code
    Code:
     With Me
        If .txtFindSRNumber > "" Then
            strFilter = & Replace(txtFindSRNumber, ", ", ", ") 
            strFilter = "[SR_Number] In ('*" & Replace(strFilter, ", ", "','") & "*')"
        End If
    .Filter = strFilter
        .FilterOn = (strFilter > "")
        End With
    when I input a value it's okay to put <123, 456> but the thing is you need to be specific. because it will post only "123" and "456" not 1234. 4567.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,578
    I see, the code incorporates the ' delimiters to define the distinct set elements. Yes, something I was mulling over and just tested. It worked. However, the LIKE and wildcards will not work in conjuction with the IN and its set.
    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.

  7. #7
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    oh I see. That's why even I play on the code around that one. It shows me error always hehe! Thanks.

    But what kind of coding can I use to perform that Like search that can be separated by comma?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,578
    I envision a more complex function that would parse the string then build a long statement with multiple LIKE and wildcard operators. So you end up with:

    [SR_Number] LIKE '*123*' OR [SR_Number] LIKE '*456*' OR ...
    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.

  9. #9
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    uhmm is that the same when I just want to search the value inputted on Text30. and not putting '*123*' ?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,578
    Don't understand the question. How would that be the same?
    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.

  11. #11
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    [SR_Number] LIKE '*123*' OR [SR_Number] LIKE '*456*' OR ...

    ^ This one is like you're searching for 123 and 456..
    what if I change the 123 and 456 to.. Me.Text30..

    Text30 is the name of the textbox and I want to get the value on that textbox. Meaning the LIKE will depend on the value putted on Text30.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,578
    Oh, my example was to show the result of a function processing input to textbox.
    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.

  13. #13
    spideynok is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    48
    hehe yes. the result on this will also seen on the subform

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

Similar Threads

  1. Search Button in a Form not Working
    By spideynok in forum Forms
    Replies: 30
    Last Post: 03-29-2012, 01:10 AM
  2. Search Button in a Form
    By spideynok in forum Forms
    Replies: 14
    Last Post: 03-18-2012, 10:08 PM
  3. Button to search table with any field in form
    By sephiroth2906 in forum Forms
    Replies: 3
    Last Post: 04-19-2011, 11:17 AM
  4. Search Button with Multiple Criteria
    By injanib in forum Forms
    Replies: 2
    Last Post: 01-12-2011, 02:21 AM
  5. search button on Form
    By josejuancruz in forum Access
    Replies: 1
    Last Post: 12-23-2010, 07:21 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
  •  
Tech Forums: Microsoft Office Forums