Sql: "in" Function Not Working For Multiple Word Search Function

[holsy]

Hi All, I have a multiple word function that I am having trouble getting the SQL IN statement to return any records. I want to return records containing ANY of the search query words. Here is the code:

Dim thisArray, i, sqlCriteria   sqlCriteria = trim(Request.QueryString("searchbox"))   thisArray = split(sqlCriteria, " ")   sSQL = "SELECT * FROM videos WHERE tags IN ('" & Join(thisArray, "','") & "')"

The sqlCriteria = trim(Request.QueryString("searchbox")) is receiving the url parameter string of: ?searchbox=socks+white+cottonThe words are seperated with a "+" sign. Somehow, the split(sqlCriteria, "+") doesn't parse the words correctly, but when I use split(sqlCriteria, " "); it works.I did a response.write sSQL that reads : SELECT * FROM videos WHERE tags IN ('socks','white','cotton')The "tags" field in the ms access database is a memo field with words seperated with a space. Here is how I open the recordset:

dim objConn, sConnectset objConn=Server.CreateObject("ADODB.connection")sConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/db_folder/mydb.mdb")objConn.ConnectionString=sConnectobjConn.openRecordset.CursorLocation = 3Recordset.open sSQL, objConn

not sure why it's not working, am I missing something?

[justsomeguy]

You're comparing the entire value of the tags field, the database doesn't just assume that the field contains a list of words and that you want to search for each word individually. One option would be to loop through the list of words you're searching for and add a LIKE clause to the query for each word.

[holsy]

How about something like this OR statement?

Dim ListArray   sqlCriteria = trim(Request.QueryString("searchbox"))     ListArray = split(sqlCriteria, " ")     For loopctr = 0 to Ubound(ListArray)   NewItem = "tags='" & ListArray(loopctr) & "'"   response.Write NewItem   response.write "<br>"   If Len(SqlCriteria) > 0 Then   JoinStatement = " OR "   Else   JoinStatement = ""   End if   MySqlCriteria = MySqlCriteria & JoinStatement & NewItem   Next     sSQL = "Select * From videos Where " & MySqlCriteria   response.Write sSQL

I found this online but it is currently spitting out garbage:Response.Write sSQL displays: Select * From videos Where OR tags='socks' OR tags='white' OR tags='cotton'The OR is inserted right after the WHERE clause. I tried to hard code the SQL statement just to test and got no returns. I don't get it, before trying using the SQL IN statements, I read from here: http://www.techonthenet.com/sql/in.php that it does work. Hmmm..confused now

[holsy]

Finally solved it with suggestion from justsomeguy:

  selCrit = "OR"  strInput = Request.form("searchbox")  arrSearch = split(strInput, " ")  strSQL = "SELECT * FROM videos WHERE"  strCombine =  join(arrSearch, "%') " & selCrit & " (tags LIKE '%")  strSQL = strSQL & " (tags LIKE '%" & strCombine & "%')"

Thanks for the suggestion. My headache is gone now