query AND/OR for textboxes

[gilgalbiblewhee]

I have 6 textboxes where if I fill them up I want the query to search if either one of them has results found. I have this but it's not working:


SQL = "SELECT * FROM bible WHERE "
SQL = SQL & "text_data LIKE '%" & Keyword & "%' OR "
SQL = SQL & "text_data LIKE '%" & Keywordb & "%' OR "
SQL = SQL & "text_data LIKE '%" & Keywordc & "%' OR "
SQL = SQL & "text_data LIKE '%" & Keywordd & "%' OR "
SQL = SQL & "text_data LIKE '%" & Keyworde & "%' OR "
SQL = SQL & "text_data LIKE '%" & Keywordf & "%' AND "
SQL = SQL & "book_spoke = '" & spoke_number & "'"

iCounter = iCounter + 1

(I would like to know the use of the iCounter since I use it from other examples but don't know why it's there.)

I've tried this too but this gives every record within
"book_spoke = '" & spoke_number & "'"

SQL = "SELECT * FROM bible WHERE "
SQL = SQL & "book_spoke = '" & spoke_number & "'"
SQL = SQL & " AND ("
SQL = SQL & "text_data LIKE '%" & Keyword & "%' OR "
SQL = SQL & "text_data LIKE '%" & Keywordb & "%' OR "
SQL = SQL & "text_data LIKE '%" & Keywordc & "%' OR "
SQL = SQL & "text_data LIKE '%" & Keywordd & "%' OR "
SQL = SQL & "text_data LIKE '%" & Keyworde & "%' OR "
SQL = SQL & "text_data LIKE '%" & Keywordf & "%'"
SQL = SQL & " )"

[buntine]

iCounter is just an incrementing variable. If you do not use it elsewhere in the application, its probably dead code and can be deleted.

What happens when you run the first query? It *shouldnt* cause an error.

Regards.

[lmf232s]

gilgalbiblewhee,
Statement looks fine, but let me guess, its returning all the records.
This is just a stab in the dark but,
you have 6 textboxes that you are building your query string off of.
If you dont fill in all 6 textboxs then you are supplying kind of a
wild card. Lets say Keywordf was a null value then in the query
string it would look like this '%%', which should mean anything,
and everything is a match.

SQL = "SELECT * FROM bible WHERE "
if request.form("txtKeyWord") <> "" then
SQL = SQL & "text_data LIKE '%" & Keyword & "%' OR "
end if

You would need to do something like this and validate that the
text has a valid value or you will supply it with '%%', which should make it select all. You would need to validate for valid data in all
6 textboxes.