Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76

    Unanswered: Apostrophe in a search string

    Hi all,

    I am having problems when I seach for a name within a search button that contains an apostrophe such as O'Neil I get runtime error 3075. Can anyone tell me how the following code shoudl change to take into account the apostrophe? We are all on Office 365. Thanks in advance.


    Function BuildSQLString(ByRef strSQL As String) As Boolean

    On Error GoTo Err_Handler

    Dim strSELECT As String
    Dim strFROM As String
    Dim strWhere As String

    strSELECT = "SELECT tblCompany.CompanyID, tblCompany.CompanyName, tblCompany.CompanyCounty, tblBuyingGroups.BuyingGroup, tblCompany.CompanyAddress, tblCompany.CompanyPostCode, tblCompany.CompanyTelephone, tblCounty.County"
    strFROM = " FROM tblCounty INNER JOIN (tblBuyingGroups RIGHT JOIN (tblCompany LEFT JOIN tblLinkBuyingGroup ON tblCompany.CompanyID = tblLinkBuyingGroup.CustomerCompanyID) ON tblBuyingGroups.BuyingGroupID = tblLinkBuyingGroup.BuyingGroupID) ON tblCounty.CountyID = tblCompany.CompanyCounty"

    If chkName Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "tblCompany.CompanyName Like " & "'" & "*" & cboName & "*" & "'"
    strSQL = strSELECT & strFROM
    End If


    If chkCounty Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "tblCounty.County = " & "'" & cboCounty & "'"
    strSQL = strSELECT & strFROM
    End If



    If chkTelephone Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    ' strWhere = strWhere & "tblCompany.CompanyTelephone = " & "'" & cboTelephone & "'"
    strWhere = strWhere & "tblCompany.CompanyTelephone Like " & "'" & cboTelephone & "*" & "'"
    strSQL = strSELECT & strFROM
    End If


    If chkBuyingGroup Then

    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "tblBuyingGroups.BuyingGroup = " & "'" & cboBuyingGroup & "'"
    strSQL = strSELECT & strFROM
    End If


    If chkPostCode Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    ' strWhere = strWhere & "tblCompany.CompanyPostcode = " & "'" & cboPostCode & "'"
    strWhere = strWhere & "tblCompany.CompanyPostCode Like " & "'" & cboPostCode & "*" & "'"
    strSQL = strSELECT & strFROM
    End If

    If strWhere <> "" Then strSQL = strSQL & " WHERE " & strWhere & " AND tblCompany.CompanyCustomer=Yes"

    BuildSQLString = True

    Exit_Error:
    Exit Function

    Err_Handler:
    MsgBox "Error Number: " & err.Number & vbCrLf & vbCrLf & "Error Description: " & err.Description, , "Error"
    Resume Exit_Error

    End Function

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You could try this
    Code:
    strWhere = strWhere & "tblCompany.CompanyName Like " & "'" & "*" & Replace(cboName,"'","''") & "*" & "'"
    If you replace all apostrophes with a double apostrophe the compiler assume you want to literally include an apostrophe and not treat it as a delimiter, the same as does with double quotation marks.

    HTH


    MTB

  3. #3
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    Thanks for this. It has been so long since I wrote this database, I am really struggling. I can understand your line of code but where would I put this in my code? What do I need to replace?

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    The replace function, unsurprisingly, replaces the what is I the second argument with what is in the third argument

    So the variable "O'Neal" when enclosed in the function like this Replace("O'Niel","'","''") would become "O''Niel". When the string is evaluated by the compiler, which would then treat it as the literal string "O'Niel"

    If no apostrophes are present then it has no effect.

    So, everywhere you have a string variable (such as cboName) concatenated into the query string the you think may have an apostrophe in it enclose it in the Replace() function.

    Hope that makes sense.

    MTB

  5. #5
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    Works like a dream. Thanks very much

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    you are welcome. Just pleased to help.

Posting Permissions

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