Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quotes and Apostrophes in SQL statements (Access97)

    Hi All,

    Searching posts turned up a series of posts dated March 9, 2001, and I still need some help.

    StrMySearchCriteria = Trim(rst![MyListOfSearchCriteria])

    StrMySearchCriteria could be one of the following:

    Smith
    O'Brian
    TOYS 'R' US
    TOYS "R" US

    strSql1 = strSql1 & " WHERE (((MyTblName.MyFieldName) = '" & strMySearchCriteria & "'))"

    How are quotes placed in the WHERE clause to handle the above criteria?

    Thanks

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quotes and Apostrophes in SQL statements (Access97)

    One option is to replace any double quotes in the string with a pair of double quotes (using the VB Replace function), so (for example) Toys "R" Us becomes Toys ""R"" Us. Then enclose this string in the WHERE clause in double qoutes. Some loungers use chr(34) in their code to make it more readable.

    So your code would look something like:

    strSql1 = strSql1 & " WHERE (((MyTblName.MyFieldName) = " & chr(34) & Replace(strMySearchCriteria, chr(34), chr(34) & chr(34),1) & chr(34) & "))"

    Since double quotes are being used as the enclosing quotes, single quotes appearing in the string don't need any special attention.

    Is there a more elegant way of doing this? Anyone? Anyone?

    Hope this helps.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quotes and Apostrophes in SQL statements (Access97)

    Tom I tried use the Replace function you suggested,
    Replace(strMySearchCriteria, chr(34), chr(34) & chr(34),1)

    I received the following error -

    Undefined function 'Replace' in expression.

    Is this function available in Access 97?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quotes and Apostrophes in SQL statements (Access97)

    Office 97 VBA doesn't have a built-in Replace function (Office 2000 and XP do have it). You can use this function instead:

    Function ReplaceString(sIn As String, sWhat As String, sBy As String)
    Dim intPos As Integer
    Dim sResult As String
    sResult = sIn
    intPos = InStr(sResult, sWhat)
    Do While intPos > 0
    sResult = Left(sResult, intPos - 1) & sBy & Mid(sResult, intPos + Len(sWhat))
    intPos = InStr(intPos + Len(sBy), sResult, sWhat)
    Loop
    ReplaceString = sResult
    End Function

    In the code provided by Tom, use

    ReplaceString(strMySearchCriteria, Chr(34), Chr(34) & Chr(34))

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quotes and Apostrophes in SQL statements (Access97)

    Thanks Tom and Hans

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Quotes and Apostrophes in SQL statements (Access97)

    What happens if sin is a Null string? Does this routine fail?
    Pat

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quotes and Apostrophes in SQL statements (Access97)

    Hi Pat,

    The ReplaceString function will return an empty string if sIn = vbNullString or sIn = "". It will raise an error message if sIn is Null, because sIn is declared as a string. You can modify the function to return an empty string "" or Null if sIn is Null. I also inserted a check for sWhat = "", because trying to replace "" leads to an infinite loop.

    Function ReplaceString(sIn, sWhat As String, sBy As String)
    Dim intPos As Integer
    Dim sResult As String
    If IsNull(sIn) Then
    ' The next instruction makes the function return Null if sIn is Null.
    ' If you want to return "" instead, omit this instruction.
    ReplaceString = Null
    Exit Function
    End If
    If sWhat = "" Then
    ReplaceString = sIn
    Exit Function
    End If
    sResult = sIn
    intPos = InStr(sResult, sWhat)
    Do While intPos > 0
    sResult = Left(sResult, intPos - 1) & sBy & Mid(sResult, intPos + Len(sWhat))
    intPos = InStr(intPos + Len(sBy), sResult, sWhat)
    Loop
    ReplaceString = sResult
    End Function

    Regards,
    Hans

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Quotes and Apostrophes in SQL statements (Access97)

    Hi Hans,
    I was alluding to the fact that sin should be defined as a variant so as to capture the fact if it is null or not.
    Thanks anyway,
    Pat

Posting Permissions

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