By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Search Multiple Data : Textbox and Search Button in Form

spideynok
P: 14
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!
Mar 19 '12 #1

✓ answered by 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).
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Command26_Click()
    2.     Dim strFilter As String, strFilters() As String
    3.     Dim intX As Integer
    4.  
    5.     With Me
    6.         strFilters = Split(.Text24, ",")
    7.         For intX = 0 To UBound(strFilters)
    8.             strFilter = strFilter & _
    9.                         Replace(" OR ([Card_Number] Like '%F*')", _
    10.                                 "%F", strFilters(intX))
    11.         Next intX
    12.         .Filter = Mid(strFilter, 5)
    13.         .FilterOn = (.Filter > "")
    14.     End With
    15. End Sub

Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,419
Yes. It's possible.

You would need to do it with separate statements ORed together like :
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String, strFilters() As String
  2. Dim intX As Integer
  3.  
  4. strFilters = Split([txtFilter], ",")
  5. strFilter = ""
  6. For intX = 0 To UBound(strFilters)
  7.     strFilter = strFilter & _
  8.                 Replace(" OR ([X] Like '%F*')", "%F", strFilters(intX))
  9. Next intX
  10. 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.
Mar 19 '12 #2

spideynok
P: 14
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command26_Click()
  2.  
  3.     Dim strFilter As String, strFilters() As String
  4.     Dim intX As Integer
  5.  
  6.     strFilters = Split([Text24], ",")
  7.     strFilter = ""
  8.     For intX = 0 To UBound(strFilters)
  9.         strFilter = strFilter & _
  10.                     Replace(" OR ([Card_Number] Like '%Me.Text24*')", "%Me.Text24", strFilters(intX))
  11.     Next intX
  12.     strFilter = Mid(strFilter, 5)        
  13.  
  14. End Sub
uhmm It's not functioning? or did I forgot to change some names? Thank you again sir :)
Mar 20 '12 #3

NeoPa
Expert Mod 15k+
P: 31,419
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).
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Command26_Click()
    2.     Dim strFilter As String, strFilters() As String
    3.     Dim intX As Integer
    4.  
    5.     With Me
    6.         strFilters = Split(.Text24, ",")
    7.         For intX = 0 To UBound(strFilters)
    8.             strFilter = strFilter & _
    9.                         Replace(" OR ([Card_Number] Like '%F*')", _
    10.                                 "%F", strFilters(intX))
    11.         Next intX
    12.         .Filter = Mid(strFilter, 5)
    13.         .FilterOn = (.Filter > "")
    14.     End With
    15. End Sub
Mar 20 '12 #4

spideynok
P: 14
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?
Mar 20 '12 #5

NeoPa
Expert Mod 15k+
P: 31,419
It was a pleasure to help - especially with such an intelligent reponse in post #3.
Mar 20 '12 #6

spideynok
P: 14
hehe Thank you so much sir :)
Mar 20 '12 #7

NeoPa
Expert Mod 15k+
P: 31,419
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.
Mar 22 '12 #8

spideynok
P: 14
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.
Mar 23 '12 #9

NeoPa
Expert Mod 15k+
P: 31,419
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command26_Click()
  2.     Dim strFilter As String, strFilters() As String
  3.     Dim intX As Integer
  4.  
  5.     With Me
  6.         strFilters = Split(Nz(.Text24, ""), ",")
  7.         For intX = 0 To UBound(strFilters)
  8.             strFilter = strFilter & _
  9.                         Replace(" OR ([Card_Number] Like '%F*')", _
  10.                                 "%F", strFilters(intX))
  11.         Next intX
  12.         .Filter = Mid(strFilter, 5)
  13.         .FilterOn = (.Filter > "")
  14.     End With
  15. 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.
Mar 23 '12 #10

spideynok
P: 14
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. :)
Mar 23 '12 #11

NeoPa
Expert Mod 15k+
P: 31,419
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 23 '12 #12

spideynok
P: 14
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!

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command26_Click()
  2.         Dim strFilter As String, strFilters() As String
  3.         Dim intX As Integer
  4.  
  5.         With Me
  6.             strFilters = Split(Nz(.Text24, ""), ",")
  7.             For intX = 0 To UBound(strFilters)
  8.                 strFilter = strFilter & _
  9.                             Replace(" OR ([Card_Number] Like '%F*')", _
  10.                                     "%F", strFilters(intX))
  11.             Next intX
  12.             .Filter = Replace(Mid(strFilter, 5), "'", Chr(34))
  13.             .FilterOn = (.Filter > "")
  14.         End With
  15. End Sub
Mar 29 '12 #13

NeoPa
Expert Mod 15k+
P: 31,419
I'm very pleased to hear it Spidey :-)
Mar 29 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.