none
Access sql Update query failing when including Apostrophes RRS feed

  • Question

  • Hi,

    I am trying to Update a field in an Access table using VBA SQL but when the update value contains and apostrophe (') I get an error. Run-Time error '3075': Syntax error (missing operator) in query expression .......

    It works perfectly fine for values that don't contain an apostrophe.

    ", Current_Last_Name = '" & CStr(Range("J" & r).Value) & "'"

    Can anyone please help me?


    • Edited by Gazza101 Wednesday, November 21, 2012 3:13 PM
    Wednesday, November 21, 2012 3:10 PM

Answers

  • Here is the first suggestion as plain text:

    ", Current_Last_Name = " & Chr(39) & Replace(CStr(Range("J" & r).Value), Chr(39), Chr(39) & Chr(39)) & Chr(39)

    I don't see why brackets should cause a problem.


    Regards, Hans Vogelaar

    • Marked as answer by Gazza101 Thursday, November 22, 2012 12:07 PM
    Wednesday, November 21, 2012 5:00 PM

All replies

  • You could use

    ", Current_Last_Name = " & Chr(39) & Replace(CStr(Range("J" & r).Value), Chr(39), Chr(39) & Chr(39)) & Chr(39)

    or (assuming that your values don't contain double quotes):

    ", Current_Last_Name = " & Chr(34) & CStr(Range("J" & r).Value) & Chr(34)

    Chr(39) = '

    Chr(34) = "


    Regards, Hans Vogelaar

    Wednesday, November 21, 2012 3:40 PM
  • Hi Hans

    I can't seem to view your first suggestion for some reason.

    I have now also encountered brackets ( ) in the data which error when I use your 2nd suggestion though.

    Is there anything I can do so all characters are excepted?

    Wednesday, November 21, 2012 4:42 PM
  • Here is the first suggestion as plain text:

    ", Current_Last_Name = " & Chr(39) & Replace(CStr(Range("J" & r).Value), Chr(39), Chr(39) & Chr(39)) & Chr(39)

    I don't see why brackets should cause a problem.


    Regards, Hans Vogelaar

    • Marked as answer by Gazza101 Thursday, November 22, 2012 12:07 PM
    Wednesday, November 21, 2012 5:00 PM
  • Thanks for that Hans.

    As usual you have provided the solution. I had to slightly change it, see below.

    ", Current_First_Name ='" & Replace(CStr(Range("J" & r).Value), Chr(39), Chr(39) & Chr(39)) & "'"

    Thanks once again.



    • Edited by Gazza101 Thursday, November 22, 2012 12:07 PM
    Thursday, November 22, 2012 12:06 PM