Searching on more than one keyword

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

I have a client table. User provides four keywords and I need to select
clients whose names contain any two of the given keywords. How do I go about
doing this selection?

Thanks
 
John,

Use the Instr() function

Assuming your kewords are KW1, KW2 etc.

SELECT [ClientName] From TblClients _
WHERE (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW2])>0) _
OR (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW3])>0) _
OR (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW4])>0) _
OR (Instr(1,[ClientName],[KW2])>0 AND Instr(1,[ClientName],[KW3])>0) _
OR (Instr(1,[ClientName],[KW2])>0 AND Instr(1,[ClientName],[KW4])>0) _
OR (Instr(1,[ClientName],[KW3])>0 AND Instr(1,[ClientName],[KW4])>0) _
ORDER BY whtaever

Regards/JK
 
You could probably simplify that a bit

SELECT tblClients.*
FROM tblClients
WHERE
ABS(Instr(1,SomeField,[Kw1]) >0 +
Instr(1,SomeField,[Kw2]) >0 +
Instr(1,SomeField,[Kw3]) >0 +
Instr(1,SomeField,[Kw4]) >0) = 2

IF you want to meet 2 or more criteria then change = 2 to >=2
If you want to meet exactly 3 criteria ... (student apply brain here)
John,

Use the Instr() function

Assuming your kewords are KW1, KW2 etc.

SELECT [ClientName] From TblClients _
WHERE (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW2])>0) _
OR (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW3])>0) _
OR (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW4])>0) _
OR (Instr(1,[ClientName],[KW2])>0 AND Instr(1,[ClientName],[KW3])>0) _
OR (Instr(1,[ClientName],[KW2])>0 AND Instr(1,[ClientName],[KW4])>0) _
OR (Instr(1,[ClientName],[KW3])>0 AND Instr(1,[ClientName],[KW4])>0) _
ORDER BY whtaever

Regards/JK

John said:
Hi

I have a client table. User provides four keywords and I need to select
clients whose names contain any two of the given keywords. How do I go
about doing this selection?

Thanks
Click to expand...
Click to expand...
 
I agree.
This is another way but as understand it, the OP John wants *at least* 2 he
should use >=2

Regards/JK

John Spencer said:
You could probably simplify that a bit

SELECT tblClients.*
FROM tblClients
WHERE
ABS(Instr(1,SomeField,[Kw1]) >0 +
Instr(1,SomeField,[Kw2]) >0 +
Instr(1,SomeField,[Kw3]) >0 +
Instr(1,SomeField,[Kw4]) >0) = 2

IF you want to meet 2 or more criteria then change = 2 to >=2
If you want to meet exactly 3 criteria ... (student apply brain here)
John,

Use the Instr() function

Assuming your kewords are KW1, KW2 etc.

SELECT [ClientName] From TblClients _
WHERE (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW2])>0) _
OR (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW3])>0) _
OR (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW4])>0) _
OR (Instr(1,[ClientName],[KW2])>0 AND Instr(1,[ClientName],[KW3])>0) _
OR (Instr(1,[ClientName],[KW2])>0 AND Instr(1,[ClientName],[KW4])>0) _
OR (Instr(1,[ClientName],[KW3])>0 AND Instr(1,[ClientName],[KW4])>0) _
ORDER BY whtaever

Regards/JK

John said:
Hi

I have a client table. User provides four keywords and I need to
select
clients whose names contain any two of the given keywords. How do I go
about doing this selection?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
I agree that is probably what the OP wanted, but it is not what was said. That
is why I posted the possible changes to the query.

"... contain any two ..." is ambiguous. It could mean exactly two or it could
mean at least two.

If it was two or more, then I would probably use SQL alone instead of involving
a VBA function like instr.

WHERE (ClientName Like "*" & [Kw1] & "*" and ClientName Like "*" & "[KW2] & "*")
OR (ClientName Like "*" & [Kw1] & "*" and ClientName Like "*" & "[KW3] & "*")
OR (ClientName Like "*" & [Kw1] & "*" and ClientName Like "*" & "[KW4] & "*")
OR (ClientName Like "*" & [Kw2] & "*" and ClientName Like "*" & "[KW3] & "*")
OR (ClientName Like "*" & [Kw2] & "*" and ClientName Like "*" & "[KW4] & "*")
OR (ClientName Like "*" & [Kw3] & "*" and ClientName Like "*" & "[KW4] & "*")
I agree.
This is another way but as understand it, the OP John wants *at least* 2 he
should use >=2

Regards/JK

John Spencer said:
You could probably simplify that a bit

SELECT tblClients.*
FROM tblClients
WHERE
ABS(Instr(1,SomeField,[Kw1]) >0 +
Instr(1,SomeField,[Kw2]) >0 +
Instr(1,SomeField,[Kw3]) >0 +
Instr(1,SomeField,[Kw4]) >0) = 2

IF you want to meet 2 or more criteria then change = 2 to >=2
If you want to meet exactly 3 criteria ... (student apply brain here)
John,

Use the Instr() function

Assuming your kewords are KW1, KW2 etc.

SELECT [ClientName] From TblClients _
WHERE (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW2])>0) _
OR (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW3])>0) _
OR (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW4])>0) _
OR (Instr(1,[ClientName],[KW2])>0 AND Instr(1,[ClientName],[KW3])>0) _
OR (Instr(1,[ClientName],[KW2])>0 AND Instr(1,[ClientName],[KW4])>0) _
OR (Instr(1,[ClientName],[KW3])>0 AND Instr(1,[ClientName],[KW4])>0) _
ORDER BY whtaever

Regards/JK

Hi

I have a client table. User provides four keywords and I need to
select
clients whose names contain any two of the given keywords. How do I go
about doing this selection?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
John,

What OP said is *any* two matches, thus I simply concluded that it does not
preclude more matches. I like your previous suggestion better, but this only
me.

BTW, I noticed that this OP has the habit of not acknowledge replies to his
posts. I wouldn't spend time on him.

Regards/JK

John Spencer said:
I agree that is probably what the OP wanted, but it is not what was said.
That
is why I posted the possible changes to the query.

"... contain any two ..." is ambiguous. It could mean exactly two or it
could
mean at least two.

If it was two or more, then I would probably use SQL alone instead of
involving
a VBA function like instr.

WHERE (ClientName Like "*" & [Kw1] & "*" and ClientName Like "*" & "[KW2]
& "*")
OR (ClientName Like "*" & [Kw1] & "*" and ClientName Like "*" & "[KW3] &
"*")
OR (ClientName Like "*" & [Kw1] & "*" and ClientName Like "*" & "[KW4] &
"*")
OR (ClientName Like "*" & [Kw2] & "*" and ClientName Like "*" & "[KW3] &
"*")
OR (ClientName Like "*" & [Kw2] & "*" and ClientName Like "*" & "[KW4] &
"*")
OR (ClientName Like "*" & [Kw3] & "*" and ClientName Like "*" & "[KW4] &
"*")
I agree.
This is another way but as understand it, the OP John wants *at least* 2
he
should use >=2

Regards/JK

John Spencer said:
You could probably simplify that a bit

SELECT tblClients.*
FROM tblClients
WHERE
ABS(Instr(1,SomeField,[Kw1]) >0 +
Instr(1,SomeField,[Kw2]) >0 +
Instr(1,SomeField,[Kw3]) >0 +
Instr(1,SomeField,[Kw4]) >0) = 2

IF you want to meet 2 or more criteria then change = 2 to >=2
If you want to meet exactly 3 criteria ... (student apply brain here)

JK wrote:

John,

Use the Instr() function

Assuming your kewords are KW1, KW2 etc.

SELECT [ClientName] From TblClients _
WHERE (Instr(1,[ClientName],[KW1])>0 AND
Instr(1,[ClientName],[KW2])>0) _
OR (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW3])>0) _
OR (Instr(1,[ClientName],[KW1])>0 AND Instr(1,[ClientName],[KW4])>0) _
OR (Instr(1,[ClientName],[KW2])>0 AND Instr(1,[ClientName],[KW3])>0) _
OR (Instr(1,[ClientName],[KW2])>0 AND Instr(1,[ClientName],[KW4])>0) _
OR (Instr(1,[ClientName],[KW3])>0 AND Instr(1,[ClientName],[KW4])>0)
_
ORDER BY whtaever

Regards/JK

Hi

I have a client table. User provides four keywords and I need to
select
clients whose names contain any two of the given keywords. How do I
go
about doing this selection?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

multiple keyword search 4
DCount on multivalue fields 2
keyword 2 2
On-not-in-list and more 1
Multiple keyword search 2
keyword table 2
Keyword Search 1
Form with dropdown/type keyword 2

Back
Top