Query criteria for multiple keywords seperated by comma

SAMPLE.accdbI have a form to show the data based on query. A textbox filters the data of query based on single keyword. I want my data to be filtered by single textbox using multiple keywords seperated by comma.

I have attaced the relevant file. In search textbox of form I want to requery the productquery. For example in search if I write (10,plain,44) then it should show me product no. 13.

Any help in this regard will be highly appreciable.
Muhammad Bilal KhawajaMills ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the IN syntax:

SELECT * FROM SomeTable WHERE YourField IN ('Val1', 'Val2','Val3')

If your users are entering these values with a comma between them, you can use Replace to get the snytax right:

Dim sVals As String
sValue = Me.YourTextbox Replace(Me.YourTextbox, ",", "','")
Dim sql As string
sql = "SELECT * FROM SomeTable WHERE YourField IN ('" & sValue &"')"
0
aikimarkCommented:
To protect your application from typos, you should eliminate any space characters before and after the comma characters before doing Scott's solution.
0
hnasrCommented:
Do you want to include the form field in the query?

Assuming the form field txtField has value 1, 2, 3
then you want to list for values in txtField?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

aikimarkCommented:
set ore=CreateObject("vbscript.regexp")
ore.global=true
ore.pattern=" *, *"
txtbox="val1 ,val2 , val3,val4"
?"(""" & ore.replace(txtbox, """,""") & """)"
("val1","val2","val3","val4")

Open in new window

0
hnasrCommented:
I suggest you upload a demo database demonstrating the issue.
Specify the expected output.
0
Muhammad Bilal KhawajaMills ManagerAuthor Commented:
SAMPLE.accdbI have attaced the relevant file. In search textbox of form I want to requery the productquery. For example in search if I write (10,plain,44) then it should show me product no. 13.

Any help in this regard will be highly appreciable.
0
hnasrCommented:
Thank you. I'll have a go shortly.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Add this code in the Click event of your button:

Dim datain() As String
datain = Split(Me.SEARCH, ",")
Dim where As String
Dim i As Integer
For i = 0 To UBound(datain)
    If Len(where) > 0 Then
        where = where & " AND "
    End If
    where = where & " PNAME LIKE ('*" & datain(i) & "*')"
Next i

Open in new window

0
hnasrCommented:
For example in search if I write (10,plain,44) then it should show me product no. 13

Open in new window

What about (plain,44,10) or (44,10,plain)?
0
hnasrCommented:
Try using form filter, check the attachment.
SAMPLE-filter.accdb
0
Muhammad Bilal KhawajaMills ManagerAuthor Commented:
I have found another solution. I can search with 2 parameters seperated by *. Thank you all for your valueable time.

SAMPLE-filter.accdb
0
Muhammad Bilal KhawajaMills ManagerAuthor Commented:
@hnasr it is also good as per your idea. can you please help me in this regard. (44,10,Plain)?

Also I want another thing. Is it possible to filter multiple items with multiple keywords. Like if I type RGG,GRS then all items containing both should be filtered.
0
Muhammad Bilal KhawajaMills ManagerAuthor Commented:
@Scott McDaniel Solution doesn't work properly.

Dim datain() As String
datain = Split(Me.SEARCH, ",")
Dim where As String
Dim i As Integer
For i = 0 To UBound(datain)
    If Len(where) > 0 Then
        where = where & " AND "
    End If
    where = where & " PNAME LIKE ('*" & datain(i) & "*')"
Next i
0
hnasrCommented:
Yes! It can be done.
Working on it.
0
hnasrCommented:
Try this. Click Search 2 button.
SAMPLE-filter-2.accdb
0
Muhammad Bilal KhawajaMills ManagerAuthor Commented:
@hnssr. Thank you so much. it works perfectly. I need another help. I want all the results which show GRS,RGG all related product having either Grs or Rgg.
0
hnasrCommented:
Here you are!
SAMPLE-filter-3.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Muhammad Bilal KhawajaMills ManagerAuthor Commented:
@hnasr. you are great man.
0
Muhammad Bilal KhawajaMills ManagerAuthor Commented:
@hnasr. can we perform all these using query rather than vba? because it will help to make report easily.
0
hnasrCommented:
Why not?
You may start a new question, attach the database, and explain the requirements.
Comment here with the link for the new question, and refer to this question in the new one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.