Keyword Search Query Help

[scouse]

Hi

I'm building a search facility that has two user inputs (Region and Service offered). Both these values are entered by the user and will be Keyword based. So for example, if a user typed in "Crash Barrier" for the service the search will search and return anything matching "Crash" and "Barrier". However, I need this to work for both user inputs (the region and the service).

I have managed to write the code that splits both inputs into keywords and then build the search query. However, there is one more variable that needs to be met on the query that I can't get working. I need the search to return only records that meet the two user inputs described above and also is the companies Active field is set to "YES".

At the moment the search returns records relevent to the user inputs but returns the records no matter what the Active field is set to!

Hope that all made sense so far :confused:

This is the code for making the query (its and SQL database btw)



' SPLITS REGION WORDS UP
Keywords = strREGION
KeywordsFixed = Keywords
' Remove any " & '.
KeywordsFixed = Replace(KeywordsFixed, Chr(34), "")
KeywordsFixed = Replace(KeywordsFixed, "'", "''")

If KeywordsFixed <> "" Then
KeyWordArray = Split(KeywordsFixed, " ")

For intItemNum = 0 To UBound(KeyWordArray)
strLIKEQuery = strLIKEQuery & " OR ("
strLIKEQuery = strLIKEQuery & "(USERS_UKREGION Like " & Chr(39) & "%" & KeyWordArray(intItemNum) & "%" & Chr(39) & ")"
strLIKEQuery = strLIKEQuery & ")"
Next
strLIKEQuery = Right(strLIKEQuery, Len(strLIKEQuery) - 4)
End If


'SPLITS SERVICES WORDS UP

Keywords2 = strSERVICES
KeywordsFixed2 = Keywords2
' Remove any " & '.
KeywordsFixed2 = Replace(KeywordsFixed2, Chr(34), "")
KeywordsFixed2 = Replace(KeywordsFixed2, "'", "''")

If KeywordsFixed2 <> "" Then
KeyWordArray2 = Split(KeywordsFixed2, " ")

For intItemNum2 = 0 To UBound(KeyWordArray2)
strLIKEQuery2 = strLIKEQuery2 & " OR ("
strLIKEQuery2 = strLIKEQuery2 & "(USERS_SERVICES Like " & Chr(39) & "%" & KeyWordArray2(intItemNum2) & "%" & Chr(39) & ")"
strLIKEQuery2 = strLIKEQuery2 & ")"
Next
strLIKEQuery2 = Right(strLIKEQuery2, Len(strLIKEQuery2) - 4)
End If

'QUERY

strUSERS = "SELECT * FROM users WHERE "&strLIKEQuery&" AND "&strLIKEQuery2&" AND USERS_ACTIVE='YES' ORDER by USERS_COMPANY ASC;"





So if the input type was:

Region: Midlands
Services: Crash Barriers

the query string becomes:



SELECT * FROM users WHERE ((USERS_UKREGION Like '%Midlands%')) AND ((USERS_SERVICES Like '%Crash%')) OR ((USERS_SERVICES Like '%Barriers%')) AND USERS_ACTIVE='YES' ORDER by USERS_COMPANY ASC;



I know this looks wrong, but I don't know what to do to make it right!

Thanks and sorry about the long post I just didn't know how to explain it briefly

Phil

[russell]

just need to re-arrange your parentheses a bit. build the asp to make the SQL look like this:

SELECT *
FROM users
WHERE USERS_UKREGION Like '%Midlands%'
AND (
USERS_SERVICES Like '%Crash%'
OR
USERS_SERVICES Like '%Barriers%'
)
AND USERS_ACTIVE='YES'
ORDER by
USERS_COMPANY ASC;

this is probably a little better even (though same result)

SELECT *
FROM users
WHERE USERS_ACTIVE='YES'
And USERS_UKREGION Like '%Midlands%'
AND (
USERS_SERVICES Like '%Crash%'
OR
USERS_SERVICES Like '%Barriers%'
)
ORDER by
USERS_COMPANY ASC;