0

I am needing to replace a single quote with nothing, using VBA. I put together this syntax, but it is reading my single quote as a comment indicator. I also tried to put two single quotes but that did not work either.

What is the proper way to run this replace statement in access 2013 VBA

DoCmd.RunSQL "update spreadsheetimport set userinput = replace(userinput, "'", "");"
June7
  • 19,874
  • 8
  • 24
  • 34
IcyPopTarts
  • 494
  • 1
  • 12
  • 25
  • You need to double-up the double quotes that are meant to be part of the string, otherwise unescaped quotes terminate the string literal and everything that follows is parsed as, well, not being part of the string. – Mathieu Guindon Nov 03 '17 at 19:30
  • @Mat'sMug - I doubled up on the double quotes so it looks like ""'"" (double, double, single, double, double) and there is no compile error, but I get a debug error of syntax error. – IcyPopTarts Nov 03 '17 at 19:32
  • 1
    You need to double up on the second set of quotes (for the empty string). So the third param to the replace function will actually be 4 double quotes or """". – Mark Elder Nov 03 '17 at 19:38
  • Or use two apostrophes for the empty string. – June7 Nov 04 '17 at 00:48
  • Another option: `replace(userinput, Chr(39), '')` But I guess that is calling a VBA function so not as desirable. – June7 Nov 07 '17 at 10:39
  • And another: `replace(userinput, '''', '')` – June7 Nov 07 '17 at 10:45

0 Answers0