Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Apostraphies in SQL

    I am having problems INSERTing records where a field has an apostraphy in it, e.g. - O'Brian. Here is my code:

    Code:
    db.Execute _
    "INSERT INTO tblNew " & _
    "(FIRSTNAME, SURNAME) " & _
    "VALUES ('" & rs1!FIRSTNAME & "', '" & rs1!SURNAME & "');"
    This creates an error when O'Brian is found. How can I get round this?

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    ' Becomes '' (2 * ' )
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Mar 2005
    Posts
    12
    Replace the one apostrophe with two. See below

    db.Execute _
    "INSERT INTO tblNew " & _
    "(FIRSTNAME, SURNAME) " & _
    "VALUES ('" & rs1!FIRSTNAME & "', '" & Replace(rs1!SURNAME, "'", "''") & "');"

  4. #4
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Thanks for the reply.

    I tried what you suggested, but I still get an error. When it gets to the surname Abbott, I get the error:

    Run-time error '3075':

    Syntax error (missing operator) in query expression ''Abbott ''.


    There are spaces after some of my surnames. Does this matter?
    Last edited by bcass; 03-02-05 at 11:24.

  5. #5
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Sorry cudayeep, my reply was in response to r123456. I will try your idea now.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Apostrophies

    db.Execute _
    "INSERT INTO tblNew " & _
    "(FIRSTNAME, SURNAME) " & _
    "VALUES ('" & rs1!FIRSTNAME & "', '" & rs1!SURNAME & "');"

    Just a thought:

    db.Execute _
    "INSERT INTO tblNew " & _
    "(FIRSTNAME, SURNAME) " & _
    "VALUES (""" & rs1!FIRSTNAME & """, """ & rs1!SURNAME & "");"

    Replace the ' with "".
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Thanks pkstormy, but I tried that one too, without success. Eitherway, kudayeep's idea worked, so thansk a lot.

Posting Permissions

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