sql error: memo field "LIKE" or "="

gilgalbiblewheel · April 22nd, 2005, 02:59 PM

The Field of the MS Access 2000's table is a "Memo" (not "number" nor "text"). From all of the below the one with no "'" works but, as you see when I take off the %% it doesn't work. Otherwise it includes any other character that comes before or after.

Code:

'  SQL = SQL & "gemetria LIKE " & number
'   SQL = SQL & "gemetria LIKE " & number & ""
'  SQL = SQL & "gemetria LIKE '" & number & "'"
'  SQL = SQL & "gemetria LIKE ' " & number & " ' "
'  SQL = SQL & "gemetria = " & number & ""
'  SQL = SQL & "gemetria = '" & number & "'"
'  SQL = SQL & "gemetria = ' " & number & " '"
  SQL = SQL & "gemetria LIKE '%" & number & "%' "

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm

EricJ · April 23rd, 2005, 03:56 AM

I can't see what you are trying to do ... By the sound of it you are trying to query something from the database like:

"SELECT Something FROM Database WHERE gemetria LIKE '" and that is where your problem starts, correct. Now from what I can see you don't know the actual value you are looking for so you use the "%"-directive to include anything that matches the number-criteria. This in should work correctly.

Please give me a bit of more detail if it is possible

gilgalbiblewheel · April 23rd, 2005, 03:56 PM

Code:

SELECT * FROM hebbooktable INNER JOIN hebrewbibletable ON hebbooktable.book = hebrewbibletable.book WHERE gemetria LIKE '%401%' ORDER BY id ASC

Here's the code in full:

Code:

SQL= "SELECT * "
SQL = SQL & " FROM hebbooktable INNER JOIN hebrewbibletable "
SQL = SQL & " ON hebbooktable.book = hebrewbibletable.book "
SQL = SQL & " 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("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   request.QueryString("gemetria")="yes" then

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

'  SQL = SQL & "gemetria LIKE " & number
'   SQL = SQL & "gemetria LIKE " & number & ""
'  SQL = SQL & "gemetria LIKE '" & number & "'"
'  SQL = SQL & "gemetria LIKE ' " & number & " ' "
'  SQL = SQL & "gemetria = " & number & ""
'  SQL = SQL & "gemetria = '" & number & "'"
'  SQL = SQL & "gemetria = ' " & number & " '"
  SQL = SQL & "gemetria LIKE '%" & number & "%' "
  iCounter = iCounter + 1

end if

If Trim(Request.QueryString("recordType")) <> "" Then
  aRecTypes = Split(Request.QueryString("recordType"), ",")
  If IsArray(aRecTypes) Then
  If iCounter > 0 Then
    SQL = SQL & " AND "
  End If
        SQL = SQL & " ("
    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

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 & "hebrewbibletable.book & chapter & verse LIKE '%" & hOutPut & "%'"

'  iCounter = iCounter + 1

end if

SQL = SQL & " ORDER BY id ASC "

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm

raj_makro · April 29th, 2005, 07:16 AM

Hi
' SQL = SQL & "gemetria LIKE " & number
    This gives an error since the memo or text type data to be enclosed by single quotes('') on both sides
' SQL = SQL & "gemetria LIKE " & number & ""
    This gives an error since the memo or text type data to be enclosed by single quotes('') on both sides
' SQL = SQL & "gemetria LIKE '" & number & "'"
   This is same as SQL = SQL & "gemetria = '" & number & "'" where actual comparision of strings is done
' SQL = SQL & "gemetria LIKE ' " & number & " ' "
   This is same as above
' SQL = SQL & "gemetria = " & number & ""
    This gives an error since the memo or text type data to be enclosed by single quotes('') on both sides
' SQL = SQL & "gemetria = '" & number & "'"
    The = operator does the actual comparision of strings
' SQL = SQL & "gemetria = ' " & number & " '"
    Same as above
  SQL = SQL & "gemetria LIKE '%" & number & "%' "
   This works correctly because the like operator works same as = operator if %% symbols are not placed.

Regards
Raj

Regards
Raj