Few Problems : Search Button Form

[spideynok]

Hi! Actually I had a code that the use is, I will search for a multiple data that can be separated by comma.



For Example : I put [apple, banana] on my textbox(exact as what you see on the []) and I have a record on my database named :
♦apple1
♦apple2
♦apple3
♦banana2
♦banana1
♦carrot1
♦carrot2

and when I press the Search Button it will show me all records that has APPLE and BANANA in the table.

but the problem of my code is when I put a value on the textbox like BANANA or whatever word it is. It will just filter my 50k+ record into 300records which is surely wrong, because it must show me the record that has BANANA word in the table.

Here's the code :

LEGEND :
• Command26 - Button that I consider as a Search/Filter Button
• Text24 - Textbox where will I put the value that I'm looking
• Card_Number - Name of the column where what I' am searching is Located.

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

I'm still playing on the code, but still I need someones help. Thank you in advanced.

[pbaldy]

Well, you can use this technique to debug the string and make sure it's coming out properly:

http://www.baldyweb.com/ImmediateWindow.htm

If you don't spot the problem, post the result here.

[June7]

You state 'show me all records that has APPLE and BANANA in the table'. Do you actually mean 'all records that have either apple OR banana'? It is the OR operator you have in code.

Your example data of apples and bananas doesn't fit the code which references Card_Number as the search field.

Why the %F?

[spideynok]

Hi! Actually I'm not directly looking for apple and banana. Just an example.

It always filter on 300 records when I put a value on the textbox.

[June7]

I can't see anything wrong with code. Use debug techniques. Provide project for analysis.

[spideynok]

Hi everyone! I'm working for this project for almost 3 weeks now, and I need to finish it. I had two set of database which has different sets of codes. I want to do this is to search multiple data separated by comma using keyword.

Like example I have a record of APPLE and BANANA.. when I put [app, bana] on my textbox it will show me all the records that has APPLE and BANANA.

But this one is for Card_Number please check it. Please help me

CODE/DB1
https://www.cx.com/mycx/share/69j4kX...OBtLIg/CSP.zip

CODE/DB2
https://www.cx.com/mycx/share/lJRs13...BtLIg/CSP2.zip

Thank you in advanced.

[June7]

Need quote marks instead of apostrophes.

.Filter = Replace(Mid(strFilter, 5), "'", Chr(34))

[spideynok]

Hi sir June7 It's working now! thank you so much! the search box and button should be placed on the subform.

But one more thing, i can search using keyword even entering comma but it couldn't search for the next value after the comma?

like I'm searching [5299671, 5299672] the only record it will show is 5299671+++++ and the 5299672 is not?

[spideynok]

yo yo yo! it works.. i shouldn't put space after the comma

[June7]

That or use Trim function on each array element.

[spideynok]

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

I used this and I place the code on a search button inside the subform. Thank you so much sir!