I think it the iCounter that's not placed properly. I often get and extra "AND". I erase the "AND" and search something else and then there is a lack of "AND".
Code:
    rs.PageSize = RECORDS_PER_PAGE
    rs.CacheSize = 5
    rs.CursorLocation = adUseClient
    Dim SQL
    Dim number
    Dim hInput
    Dim hOutput
    Dim iCounter
    Dim Keyword     ' Keyword for search
    Dim Keywordb
    Dim Keywordc
    Dim Keywordd
    Dim Keyworde
    Dim Keywordf
    Dim spoke
    Dim aRecTypes
    Dim iLoopCount
    Dim recordType

        ' Let's see what user wants to search for today :)
    Keyword = Trim(Request.QueryString("Keyword"))
    Keywordb = Trim(Request.QueryString("Keywordb"))
    Keywordc = Trim(Request.QueryString("Keywordc"))
    Keywordd = Trim(Request.QueryString("Keywordd"))
    Keyworde = Trim(Request.QueryString("Keyworde"))
    Keywordf = Trim(Request.QueryString("Keywordf"))
    spoke = Request.Querystring("spoke")
    number = Request.QueryString("number")
    hInput = Request.QueryString("hInput")
    hOutput = Request.QueryString("hOutput")
    recordType = Request.QueryString("hebrewbibletable.recordType")
    iCounter = 0

SQL= "SELECT * "
SQL = SQL & " FROM hebbooktable INNER JOIN hebrewbibletable "
SQL = SQL & " ON hebbooktable.book = hebrewbibletable.book "
SQL = SQL & " WHERE "

'    SQL = "SELECT * FROM 2 WHERE "

If Keyword <> "" Then
  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If
    SQL = SQL & "text_data LIKE '%" & Keyword & "%' "
      iCounter = iCounter + 1
End If 
If Keywordb <> "" Then
  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If
    SQL = SQL & "text_data LIKE '%" & Keywordb & "%' "
      iCounter = iCounter + 1
End If 
If Keywordc <> "" Then
  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If
    SQL = SQL & "text_data LIKE '%" & Keywordc & "%' "
      iCounter = iCounter + 1
End If 
If Keywordd <> "" Then
  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If
    SQL = SQL & "text_data LIKE '%" & Keywordd & "%' "
      iCounter = iCounter + 1
End If 
If Keyworde <> "" Then
  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If
    SQL = SQL & "text_data LIKE '%" & Keyworde & "%' "
      iCounter = iCounter + 1
End If 
If Keywordf <> "" Then
  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If
    SQL = SQL & "text_data LIKE '%" & Keywordf & "%' "
      iCounter = iCounter + 1
End If 

If   request.QueryString("book_spoke")="Book_Spoke" then

  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If
  SQL = SQL & "book_spoke LIKE '" & spoke & "'"
  iCounter = iCounter + 1
end if

If   request.QueryString("chapter_spoke")="Chapter_Spoke" then

  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If

  SQL = SQL & "chapter_spoke LIKE '" & spoke & "'"

  iCounter = iCounter + 1

end if

If   request.QueryString("verse_spoke")="Verse_Spoke" then

  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If

  SQL = SQL & "verse_spoke LIKE '" & spoke & "'"

  iCounter = iCounter + 1

end if


'If number <> "" Then

If   request.QueryString("hebbooktable.book")="yes" then

  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If
  SQL = SQL & "hebbooktable.book LIKE '" & number & "'"
  iCounter = iCounter + 1
end if

If   request.QueryString("chapter")="yes" then

  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If

  SQL = SQL & "chapter LIKE '" & number & "'"

  iCounter = iCounter + 1

end if

If   request.QueryString("verse")="yes" then

  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If

  SQL = SQL & "verse LIKE '" & number & "'"

  iCounter = iCounter + 1

end if
If   request.QueryString("bookchapter")="yes" then

  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If

  SQL = SQL & "hebrewbibletable.book & chapter LIKE '%" & number & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("chapterverse")="yes" then

  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If

  SQL = SQL & "chapter & verse LIKE '%" & number & "%'"

  iCounter = iCounter + 1

end if
If   request.QueryString("bookchapterverse")="yes" then

  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If

  SQL = SQL & "hebrewbibletable.book & chapter & verse LIKE '%" & number & "%'"

  iCounter = iCounter + 1

end if

If Trim(Request.QueryString("recordType")) <> "" Then
  aRecTypes = Split(Request.QueryString("recordType"), ",")
  If IsArray(aRecTypes) Then
        SQL = SQL & " AND ("
    For iLoopCount = 0 To UBound(aRecTypes)
      If iLoopCount <> 0 Then
          SQL = SQL & " OR "
      End If
          SQL = SQL & "recordType = '" & trim(aRecTypes(iLoopCount)) & "'"
    Next
  End If
      SQL = SQL & ")"
iCounter = iCounter + 1
End If

'End If
If hInPut <> "" Then
  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If

  SQL = SQL & "text_data LIKE '%" & hInPut & "%'"

  iCounter = iCounter + 1

end if

If hOutPut <> "" Then

  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If

  SQL = SQL & "book & chapter & verse LIKE '%" & hOutPut & "%'"

'  iCounter = iCounter + 1

end if

SQL = SQL & " ORDER BY id ASC "