query AND/OR for textboxes

gilgalbiblewhee · #1 (**permalink**) 02-04-05, 19:16

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:

Code:

  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 & "'"

Code:

  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 & " )"

White Knight · #2 (**permalink**) 02-05-05, 20:54

the second one is more correct!

BUT you need to check each of your text inputs to see if they have anything in them...

IF they do then you add them to the query otherwise you don't

Dim sql1

SQL = "SELECT * FROM bible WHERE book_spoke = '" & spoke_number & "'"
IF Len(keyword) > 0 then
SQL1 & " OR text_data LIKE '%" & Keyword & "%'"
End if

IF len(keywordb) > 0 then
SQL1 = SQL1 & " OR text_data LIKE '%" & Keywordb & "%'"
End if

'etc

IF len(SQL1)> 0 then
SQL = SQL & " AND (" & left(SQL1, 5) & ")"
' we remove the " OR " off the beginning with left
END if

Internet Services

Web Development

Digital Marketing

Consumer Tech