Searching a table using a query for multiple words

[Phantom_guitarist]

I have a form with a text box where a user can enter a word. The
application then searches a table for any occurence of that word in the
description field. This works ok for single words, but I wish for the user
to be able to enter several words and the query uses 'OR' logic to find
these words.

Currently the SQL is:

SELECT tblStock.ID, tblStock.[Part Number], tblStock.[Product Description],
tblStock.[Unit Cost], tblStock.Quantity
FROM tblStock
WHERE (((tblStock.[Product Description]) Like "*"+[searchkeyword]+"*"));

where [searchword] is the text box on the form. I thought about trying to
use the Replace function to replace the spaces with SQL but it got a little
comlicated and it ended up with several errors and it was too difficult to
debug.

If anyone could suggest a way around this I would be very gratefull.

Stefan

 

[Douglas J. Steele]

The problem is that the actual query that needs to be generated is along the
lines of

SELECT tblStock.ID, tblStock.[Part Number], tblStock.[Product Description],
tblStock.[Unit Cost], tblStock.Quantity
FROM tblStock
WHERE tblStock.[Product Description] Like "*Widget*"
OR tblStock.[Product Description] Like "*Thingee*"
OR tblStock.[Product Description] Like "*Gadget*"

not

SELECT tblStock.ID, tblStock.[Part Number], tblStock.[Product Description],
tblStock.[Unit Cost], tblStock.Quantity
FROM tblStock
WHERE (((tblStock.[Product Description]) Like "*Widget*" Or "*Thingee*" Or
"*Gadget*"));

Take a look at http://support.microsoft.com/?id=210530 for one approach to
solve this problem.

 

[Andi Mayer]

I have a form with a text box where a user can enter a word. The
application then searches a table for any occurence of that word in the
description field. This works ok for single words, but I wish for the user
to be able to enter several words and the query uses 'OR' logic to find
these words.

Currently the SQL is:

SELECT tblStock.ID, tblStock.[Part Number], tblStock.[Product Description],
tblStock.[Unit Cost], tblStock.Quantity
FROM tblStock
WHERE (((tblStock.[Product Description]) Like "*"+[searchkeyword]+"*"));

where [searchword] is the text box on the form. I thought about trying to
use the Replace function to replace the spaces with SQL but it got a little
comlicated and it ended up with several errors and it was too difficult to
debug.

If anyone could suggest a way around this I would be very gratefull.

dim ary as string
dim filter as string
dimI as long
ary=split(theSearchWords," ")

for i=0 to ubound(ary)
filter=filter & " AND Product Description Like '*" &ary(i) &"*'"
next I

filter= "WHERE " &mid(filter,4)

SQL="SELECT ID, [Part Number], tblStock.[Product Description], " _
&" [Unit Cost], Quantity " _
&" FROM tblStock " &Filter

Error checking is missing !!!!



If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[Andi Mayer]

I have a form with a text box where a user can enter a word. The
application then searches a table for any occurence of that word in the
description field. This works ok for single words, but I wish for the user
to be able to enter several words and the query uses 'OR' logic to find
these words.

Currently the SQL is:

SELECT tblStock.ID, tblStock.[Part Number], tblStock.[Product Description],
tblStock.[Unit Cost], tblStock.Quantity
FROM tblStock
WHERE (((tblStock.[Product Description]) Like "*"+[searchkeyword]+"*"));

where [searchword] is the text box on the form. I thought about trying to
use the Replace function to replace the spaces with SQL but it got a little
comlicated and it ended up with several errors and it was too difficult to
debug.

If anyone could suggest a way around this I would be very gratefull.

dim ary as string
dim filter as string
dimI as long
ary=split(theSearchWords," ")

for i=0 to ubound(ary)
filter=filter & " AND Product Description Like '*" &ary(i) &"*'"
next I

filter= "WHERE " &mid(filter,4)

SQL="SELECT ID, [Part Number], tblStock.[Product Description], " _
&" [Unit Cost], Quantity " _
&" FROM tblStock " &Filter

Error checking is missing !!!!

Oppps change the AND to OR


If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW