Help with syntax

[Richard]

Hi

I have a part of an "INSERT" syntax sourced from a recordset:

strSql = "INSERT into TableA ( Signage ) VALUES (" & """" & Rs!signage &
"""" & ") "

If my signage consist of a " double quote", I will get an error.

If I use "VALUES (" & " ' " & Rs!signage & " ' " ......

it will be okay unless I have an apostrophe in the signage.

How will I create a syntax that will accomodate both, " and ' .?

Thanks in advance
Richard

 

[Anushi]

Richard,

Escape your double quote character with a backslash. \ is the "escape
character" meaning that the next
character should not be interpreted as a especial char.

Try this:

strSql = "INSERT into TableA ( Signage ) VALUES (" & " \"" & Rs!signage &
"\"" & ") "

HTH
Anushi

 

[Douglas J. Steele]

Unfortunately, \ isn't an escape character in VBA, so your suggestion won't
help.

 

[Douglas J. Steele]

Whichever delimiter you use, you need to double any occurrences of the
delimiter that occur inside the string. Assuming you're using Access 2000 or
newer, you can use the built-in Replace function to help you with this.

Either of the following will work:

strSql = "INSERT into TableA ( Signage ) VALUES (" & """" &
Replace(Rs!signage, """", """""") &
"""" & ") "

strSql = "INSERT into TableA ( Signage ) VALUES (" & "'" &
Replace(Rs!signage, "'", "''") &
"'" & ") "

BTW, rather than having """", you can use Chr$(34) to represent a double
quote (or Chr$(39) to represent a single quote), so the above could also be
written as:

strSql = "INSERT into TableA ( Signage ) VALUES (" & Chr$(34) &
Replace(Rs!signage, Chr$(34), Chr$(34) & Chr$(34)) & Chr$(34) & ") "

strSql = "INSERT into TableA ( Signage ) VALUES (" & Chr$(39) &
Replace(Rs!signage, Chr$(39), Chr$(39) & Chr$(39)) & Chr$(39) & ") "

Doing can reduce problems due to miscounting the number of quotes you've
used.

If you're using Access 97 or earlier, there's no built-in Replace function.
You'll need to write your own equivalent function. One possible solution is
shown in http://www.mvps.org/access/strings/str0004.htm at "The Access Web"

 

[Richard]

Thanks to both of you taking time to answer

Will try accordingly

Richard