SQL search on keywords help

[dreamer610]

Hi,
I need some help with creating an SQL query of data in an Access database. I have an ASP.net page which has a text box that users can enter keywords in. When the user clicks a Search button, the page queries an Access database which has a keywords field.

The code I have is this

SELECT * FROM tblGallery
WHERE Keywords LIKE '%" & strSearch &"%' AND ShowDate <=Date()

The value that is stored in the strSearch variable is the value that was entered in the search textbox.

This query works, but not exactly how I want it to. I want to be able to enter two or more words, which are not (necessarily)next to each other in the database field, and still have it return that row. For example, a user should be able to enter "beach sand" in the text box and get a hit even though the keyword field has "beach ocean sand" in it. Similarly I would want them to be able to enter "sand beach" and still get a hit. I understand why my SQL statement isn't working how I want it to, but I don't know how to correctly word it. Thanks in advance.

 

[Golom]

You could try

SELECT * FROM tblGallery 
WHERE Keywords LIKE '%" & [COLOR=blue]Replace(strSearch," ","%")[/color]  &"%' 
AND ShowDate <=Date()

That should give you some of what you want but it will find the specified words only if they are in the same order as provided in strSearch. (i.e. it will find beach ocean sand but not sand ocean beach. If you need the later then you need to build the SQL dynamically; testing for each word separately.

 

[dreamer610]

Thank's much! That helped a great deal.. I still would like to get it to search in any order that it's entered. Anyone have any ideas on how to most easily accomplish this?

 

[PHV]

Typed, untested:
arSearch = Split(strSearch)
strSQL = "SELECT * FROM tblGallery WHERE Keywords LIKE '%" & arSearch(0) & "%'"
For i = 1 To UBound(arSearch)
strSQL = strSql & " AND Keywords LIKE '%" & arSearch(i) & "%'"
Next
strSQL = strSql & " AND ShowDate<=Date()"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dreamer610]

Thank you, that did it. I really appreciate both of your guys help!