Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: query AND/OR for textboxes

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

  2. #2
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    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
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •