Muhammad Bilal Khawaja
asked on
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.
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.
Last Comment
To protect your application from typos, you should eliminate any space characters before and after the comma characters before doing Scott's solution.
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?
Assuming the form field txtField has value 1, 2, 3
then you want to list for values in txtField?
set ore=CreateObject("vbscript.regexp")
ore.global=true
ore.pattern=" *, *"
txtbox="val1 ,val2 , val3,val4"
?"(""" & ore.replace(txtbox, """,""") & """)"
("val1","val2","val3","val4")
I suggest you upload a demo database demonstrating the issue.
Specify the expected output.
Specify the expected output.
ASKER
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.
Any help in this regard will be highly appreciable.
Thank you. I'll have a go shortly.
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
For example in search if I write (10,plain,44) then it should show me product no. 13
Try using form filter, check the attachment.
SAMPLE-filter.accdb
SAMPLE-filter.accdb
ASKER
I have found another solution. I can search with 2 parameters seperated by *. Thank you all for your valueable time.
SAMPLE-filter.accdb
SAMPLE-filter.accdb
ASKER
@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.
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.
ASKER
@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
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
Yes! It can be done.
Working on it.
Working on it.
Try this. Click Search 2 button.
SAMPLE-filter-2.accdb
SAMPLE-filter-2.accdb
ASKER
@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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
@hnasr. you are great man.
ASKER
@hnasr. can we perform all these using query rather than vba? because it will help to make report easily.
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.
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.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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 &"')"