Results 1 to 12 of 12
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Apostrophe in the name

    The problem comes about when there is an apostrophe in the name. And produces a runtime error.


    1st Example
    DoCmd.RunSQL "INSERT INTO Invoice_Comments ( Accounts_Reference, Supplier_Code, " _
    & " Supplier_Name, Invoice_No, ResponsibleRegion, Comments_By, Comment_Date, Comments ) " _
    & "SELECT '" & sAcc_Ref & "', '" & sSup_Code & "', '" & sSup_Name & "', '" & sInv_No & "', " _
    & "'" & sRegion & "','" & sCommentsBy & "', '" & dCom_Dte & "', '" & sComments & "';"

    2nd Example
    DoCmd.RunSQL "INSERT INTO Invoice_Comments ( Accounts_Reference, Supplier_Code, " _
    & " Supplier_Name, Invoice_No, ResponsibleRegion, Comments_By, Comment_Date, Comments ) " _
    & "SELECT '" & sAcc_Ref & "', '" & sSup_Code & "', '" & Replace(sSup_Name, "'", "' '") & "', '" & sInv_No & "', " _
    & "'" & sRegion & "','" & sCommentsBy & "', '" & dCom_Dte & "', '" & sComments & "';"



    Example 1 gives me an error 3134

    I have tried entering REPLACE in front as shown in the second example of the code. This then gives me an Error 3075


    All help apprecitated.
    Cheers

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It helps if you use the code tags - the code is then in a fixed width font and helps reading. This is especially important when dealing with apostrophes.
    Code:
    ...." & Replace(sSup_Name, "'", "''") & "....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Thanks for your reply
    However, I dont understand that looks the same as what I wrote. Can you explain a little further in simple language. I am struggling with this one.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok.

    Change the code to:
    Code:
    Dim s as String
    
    s = "INSERT INTO Invoice_Comments ( Accounts_Reference, Supplier_Code, " _
         & " Supplier_Name, Invoice_No, ResponsibleRegion, Comments_By, Comment_Date, Comments ) " _
     & "SELECT '" & sAcc_Ref & "', '" & sSup_Code & "', '" & Replace(sSup_Name, "'", "' '") & "', '" & sInv_No & "', " _
         & "'" & sRegion & "','" & sCommentsBy & "', '" & dCom_Dte & "', '" & sComments & "';"
    
    Debug.Print s
    
    DoCmd.RunSQL s
    Copy and paste what the result of s is. Tell us exactly what the error reads.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    In the Immediate Window this is what i get.

    INSERT INTO Invoice_Comments ( Accounts_Reference, Supplier_Code, Supplier_Name, Invoice_No, ResponsibleRegion, Comments_By, Comment_Date, Comments ) SELECT '0010065742', '0000677900', 'ST MARY' 'S', '8203', 'South','RT', '21/07/2008 16:55:21', 'Test comment';

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    And the error is Run-Time error ‘3075’
    Syntax error (missing operator) in query expression “St Mary’2”.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please put in code tags.

    (EDIT - No need to answer - we posted simultaneously) What error?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You used your REPLACE() code not mine huh? Now - this illustrates exactly why you should use code tags. Because:
    Quote Originally Posted by HelpMePlease
    that looks the same as what I wrote.
    that is not true. If you compare the two lines in fixed width font you will see a difference.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Beg your pardon - my fault. I asked you to run your code. Compare my replace and your replace - you'll see there is a difference.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    INSERT INTO Invoice_Comments ( Accounts_Reference, Supplier_Code, Supplier_Name, Invoice_No, ResponsibleRegion, Comments_By, Comment_Date, Comments ) SELECT '', '0010065742', ....ST MARY''S...., '8203', 'South','RT', '21/07/2008 16:55:21', 'Test comment';

    And the error message os the same

  11. #11
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Ok I think I have resolved this one with changing the replace to

    '" & Replace(sSup_Name, "'", "") & "'

    many thanks for your help.

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To handle apostrophies in select statements, I've used syntax such as:

    strSQL = "Select * from MyTable where LastName like """ & SomeVariable & """"

    Using the 3 ", I've avoided issues with a ' in the name (and having to issue a replace).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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