Help with syntax

R

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
 
A

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
 
D

Douglas J. Steele

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

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"
 
Top