Multiple word search criteria in a query

R

ramez75

Registered User.
Local time
Today, 11:24
Joined
Dec 23, 2008
Messages
181
Hi,

I have a query currently with one word search criteria. So the user can enter a word and the query will bring out everything equal or similar to the word entered by the user. I am using Like "*" & [Forms]![Search]![Text1] & "*". So far so good
I was asked if I can allow the User to run the query by allowing to enter multiple words and the query comes up with all that applies. Example the User want to find all records that contains something LIKE "663", "PLC" and "Hot". So the query should give me all records that has 66345, or 663789 or 663 or PLCD, or Hotline, etc

How can I get to do that

Thanks

Ramez
 
In SQL View type
Where
Code: 
 (Field1name Like "*" & [Forms]![Search]![Text1] & "*" and [Forms]![Search]![Text1] is not null) Or (field2name Like etc

if you ever switch back to design view you will have a heart attack with what is placed in the design grid by the system !

to get all records you will need to enter an * in the parameter boxes.

Brian
 
If you run the query from a form you can use some VBA-code, to made it works.
 
So from the below "Field1name" I presume is "tbltest.Description" so what is "Field2name".
I am only searching the field "Description" in tbltest for multiple criteria. So if the USER enter in "Text1" multiple words separated by commas how will the below work. I will need an or function and something to capture the different words. I dont see how it works with the below expression.

I guess I can create in the search form "Text1", "Text2"......."Text20" but then you will need to enter data in the 20 fields but what if there is 30 word criteria???? Thats why i was thinking to use only "Text1" and separate the word criteria by ","

Brianwarnock said:
In SQL View type
Where
Code: 
 (Field1name Like "*" & [Forms]![Search]![Text1] & "*" and [Forms]![Search]![Text1] is not null) Or (field2name Like etc

if you ever switch back to design view you will have a heart attack with what is placed in the design grid by the system !

to get all records you will need to enter an * in the parameter boxes.

Brian
Click to expand...
 
JHB said:
If you run the query from a form you can use some VBA-code, to made it works.
Click to expand...

I am actually using a form were the USER enters a word and click search button to run the query. Ofcourse they have to enter one word at a time to search the "description" field in tbltest.
 
Did you look at the link and database I posted?
 
I misunderstood your requirement , looks like Jdraws has the answer.

Brian
 
jdraw said:
Did you look at the link and database I posted?
Click to expand...

Yes, i am looking at it and trying to understand how it works. So it seems i need to look at "Private Sub search_Click()". I cant understand still how it combines the words from text0 and show the records. Also i typed 2 words "physics,biochemistry" and thought that it will give me all records that has those words but it didnt.

So I am still not following
 
jdraw said:
Did you look at the link and database I posted?
Click to expand...

So the below code does the search but how can it be modified to allow "Text0" multiple words separated by commas

Code: 
Option Compare Database
Private Sub search_Click()
Dim sWords As Variant
Dim whereClause As String
Dim sql As String
   
sql = "SELECT * from dbo_Items where "
Me.Text0.Value = Trim(Me.Text0.Value) & " "
Me.Text0.Value = Replace(Me.Text0.Value, "  ", " ")
sWords = Split(Me.Text0.Value, " ")
For i = LBound(sWords) To UBound(sWords) - 1 Step 1
 
 '  Build the where clause
    If i = UBound(sWords) - 1 Then
        whereClause = whereClause & " Title Like '*" & sWords(i) & "*'"
    Else
        whereClause = whereClause & " Title Like '*" & sWords(i) & "*'  AND "
    End If
Next i
Debug.Print whereClause
'  If there are no matching records provide a message
If DCount("*", "dbo_items", whereClause) = 0 Then
    MsgBox "There are no records with those elements in the Title", vbOKOnly
    Exit Sub
End If
'  Output the results to the proper destination
     DoCmd.OpenForm "FoundItems", acFormDS, , whereClause
 
End Sub
 
Last edited:
Try with a space between words. The original was not using comma separated terms.

It was spaces between string fragments, as I recall.



If you want to have words or strings separated by comma, then
(untested)
I would start by changing this line

Code: 
sWords = Split(Me.Text0.Value, " ")

to
Code: 
sWords = Split(Me.Text0.Value, "[COLOR="Red"],[B][/B][/COLOR]")
 
Last edited:
I did add the comma, but it seems that it only searchs for the first word I have in Text0


jdraw said:
Try with a space between words. The original was not using comma separated terms.

It was spaces between string fragments, as I recall.



If you want to have words or strings separated by comma, then
(untested)
I would start by changing this line

Code: 
sWords = Split(Me.Text0.Value, " ")

to
Code: 
sWords = Split(Me.Text0.Value, "[COLOR=red],[/COLOR]")
Click to expand...
 
Can you post a copy of your database --nothing private/confidential?

I've been offline with dsl problems.
 
Try with "OR instead of "AND":
whereClause = whereClause & " Title Like '*" & sWords(i) & "*' AND "
Click to expand...
change it to:
whereClause = whereClause & " Title Like '*" & sWords(i) & "*' OR "
Click to expand...
 
He has said that he is only searching one field with multiple criteria, and the criteria can be partial thus I believe ruling out a vba solution akin to IN.

I assumed that the link posted earlier stored the data in an array and then ran through the array testing with the Instr function.

Brian
 
Possibly, but in the data base (s) I referenced, the search term didn't necessarily consist of a full word. It accommodated a number of fragments.

eg. organic or organ gan anic etc.
org chem
gan istry

to find records with say organic chemistry.

IN could work with full words (exact spelling).
 
I have attached a copy of my test database.

So what I have been trying to do is instead of having the records show in the list I want it to open up in the query format so I can move it to excel for further analysis.

Currently when I type lets say "66345,may" in the search field it will only show the "Description" field data but I want to see the whole row.

I hope I am making sense

jdraw said:
Can you post a copy of your database --nothing private/confidential?

I've been offline with dsl problems.
Click to expand...
 

Attachments

I looked at this database. It only looks one word at a time which what I have currently. I am using Like "*" & [Forms]![frmSearch]![txtSearch] & "*" in the criteria part of the query. I am trying to be able to type in multiple word or partial words separated by commas and get all the records that meet the criteria. So I guess what do I need to modify to get that


jdraw said:
I believe the database in post 56 of
http://www.accessforums.net/queries/true-keyword-search-19909/index4.html

uses "OR" and "AND" with a combination of fields.

I haven't had time to review it-- am having DSL issues.

Take a look, or post a copy of your database.
Click to expand...
 
Can we be very clear on meet the criteria.

Any word or part word in any field

All words or part words in any one field

Any word or part word in any one field
 
You must log in or register to reply here.

Similar threads

A
Mail merge query with multiple records
Replies
10
Views
271
ebs17
E
D
Table Search and Total
Replies
18
Views
303
DDJKSA
D
murray83
Solved Criteria based of text box on form not working
Replies
4
Views
92
Gasman
Gasman
H
Solved Query with multiple criteria
Replies
6
Views
482
Pat Hartman
P
Uncle Gizmo
1984 New Speak in a classroom near you .....
Replies
5
Views
349
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom