Search Multiple Data : Textbox and Search Button in Form

[spideynok]

Hi! I had a code that can search, just enter any keyword like. If I want to search "Access" I can put Acc only and then it will show me all record that has "Acc" on the database like Access, Accelerate, etc.

My question is. It is possible if I also do that in multiple search? Using comma as a separator?

For example : I will put [Acc, Ex] on my textbox. Is it possible for it to show me the record like.
•Access
•Accelerate
•Accurate
•Example
•T-Rex
•Excel

Is it possible? Thank you in advanced!

[NeoPa]

Yes. It's possible.

You would need to do it with separate statements ORed together like :

Dim strFilter As String, strFilters() As String
Dim intX As Integer
 
strFilters = Split([txtFilter], ",")
strFilter = ""
For intX = 0 To UBound(strFilters)
    strFilter = strFilter & _
                Replace(" OR ([X] Like '%F*')", "%F", strFilters(intX))
Next intX
strFilter = Mid(strFilter, 5)

You will need to replace the names I've used with ones that are in your project as you didn't include this basic information in your question.

[spideynok]

Thank You Sir NeoPa.

BTW I changed some names as what you said. I came up with this.

LEGEND
• Text24 = Textbox where I will input the value that I want to search.
• Card_Number = Name of the column where the data stored that I want to search.

Private Sub Command26_Click()
 
    Dim strFilter As String, strFilters() As String
    Dim intX As Integer
 
    strFilters = Split([Text24], ",")
    strFilter = ""
    For intX = 0 To UBound(strFilters)
        strFilter = strFilter & _
                    Replace(" OR ([Card_Number] Like '%Me.Text24*')", "%Me.Text24", strFilters(intX))
    Next intX
    strFilter = Mid(strFilter, 5)        
 
End Sub

uhmm It's not functioning? or did I forgot to change some names? Thank you again sir :)

[NeoPa]

Fundamentally you've done a great job, and posting exactly what you've done with the legend makes it much easier to focus on what you've done.

1. All the replacements you made seem fine. Line #10 (of your code) however, changes %F to %Me.Text24, which is unnecessary. It will still work perfectly, but the string is arbitrary (It doesn't matter what the string is as long as the substring in the first parameter matches the string in the second one). It's easier to leave that as %F.
2. The code produces the value for the filter. I imagine that works perfectly. You still need to apply the filter though. The code doesn't include that part as it was assumed to be understood. I'll include it in this example though, on the assumption you wish to filter the current form (You didn't say what you were doing with it so it made sense you didn't need that explained or illustrated).
   

   Private Sub Command26_Click()
       Dim strFilter As String, strFilters() As String
       Dim intX As Integer
    
       With Me
           strFilters = Split(.Text24, ",")
           For intX = 0 To UBound(strFilters)
               strFilter = strFilter & _
                           Replace(" OR ([Card_Number] Like '%F*')", _
                                   "%F", strFilters(intX))
           Next intX
           .Filter = Mid(strFilter, 5)
           .FilterOn = (.Filter > "")
       End With
   End Sub

[spideynok]

It Works! Thank you thank you thank you sir!

but the thing is. I can't use the textbox as a search box? uhmm when I enter a value. It will filter to 300 data. not including the one I putted?

[NeoPa]

It was a pleasure to help - especially with such an intelligent reponse in post #3.

[spideynok]

hehe Thank you so much sir :)

[NeoPa]

I'm afraid I didn't even notice there was any follow-on to the question.

Now I see it I have no idea what you mean. What you say doesn't make sense o.O

If you can explain it more clearly I'll see if I can help.

PS. Anyone else interested can continue to follow the thread but I will need to delete the post from redhorse as it's a hijack which is not allowed.

[spideynok]

Hi sir. I'm sorry for that unclear question.

What I was saying is that when I click the button which I use as a Search Button using NULL value it filters my 50k++ record into 300 records, and when I put a value on it like a Card Number, It still shows me the 300 records and not looking for the value that I put on the textbox.

[NeoPa]

It appears there is a problem handling Nulls, although I would expect an error message and a line number if you had this problem - not failed filtering. See below for a fixed version.

Private Sub Command26_Click()
    Dim strFilter As String, strFilters() As String
    Dim intX As Integer
 
    With Me
        strFilters = Split(Nz(.Text24, ""), ",")
        For intX = 0 To UBound(strFilters)
            strFilter = strFilter & _
                        Replace(" OR ([Card_Number] Like '%F*')", _
                                "%F", strFilters(intX))
        Next intX
        .Filter = Mid(strFilter, 5)
        .FilterOn = (.Filter > "")
    End With
End Sub

However, if a value entered is not being used for the filtering then only you can explain that. That isn't a problem with the code, but with your attempt to implement it. As I have no access to your database I cannot help you there.

[spideynok]

Thank you sir. The Null issue was solved. I will try and play on the code on when I enter a value it will look for the value that I enter. Because as of now the code given on post #10, It filters the record to 300 record even I put a Card Number which doesn't exist. Thank you Thank you sir. :)

[NeoPa]

If you're having that problem then print the .Filter string to the Immediate Pane (See Debugging in VBA).

Add the following code after line #13 :

Debug.Print .Filter

When it's run and failed to produce the expected output check out what has been printed. If it doesn't answer your question you could try posting the string on here for us to look at.

[spideynok]

Hi NeoPa! Thank you thank you so much! The error is on me, The problem is I'm putting the code to the search button on the mainform not on a subform, then when I try to put the code on the search button in the subform. It WORKS!!! THANK YOU THANK YOU THANK YOU!

Private Sub Command26_Click()
        Dim strFilter As String, strFilters() As String
        Dim intX As Integer
 
        With Me
            strFilters = Split(Nz(.Text24, ""), ",")
            For intX = 0 To UBound(strFilters)
                strFilter = strFilter & _
                            Replace(" OR ([Card_Number] Like '%F*')", _
                                    "%F", strFilters(intX))
            Next intX
            .Filter = Replace(Mid(strFilter, 5), "'", Chr(34))
            .FilterOn = (.Filter > "")
        End With
End Sub

[NeoPa]

I'm very pleased to hear it Spidey :-)