Single and Double Quotes

[bacadman]

Hello All,

I am new to vba am and encountering some issues when attempting to run a sql
statement. I am having issues with Single Quotes and Double quotes causing
errors. If the values contain any single or double quotes I get an error.
See code below.

strSQL = "insert into newDB values ('" _
& rs.Fields(0).Value & "', '" &
rs.Fields(1).Value _
& "', '" & rs.Fields(2).Value & "', '" &
rs.Fields(3).Value _
& "', '" & rs.Fields(4).Value & "', '" &
rs.Fields(5).Value _
& "', '" & rs.Fields(6).Value & "', '" &
rs.Fields(7).Value _
& "', '" & rs.Fields(8).Value & "', '" &
rs.Fields(9).Value _
& "', '" & rs.Fields(10).Value & "', '" &
rs.Fields(11).Value _
& "', '" & rs.Fields(12).Value & "', '" &
rs.Fields(13).Value _
& "', '" & rs.Fields(14).Value & "' )" '
DoCmd.RunSQL strSQL

Any assistence is appreciated.
Thanks in advance.

 

[Sreedhar]

Hi,

Why would you use quotes at all ? Remove all the quotes and try the sql.

BTW, the default property of a field is Value. Don't need to assign it
explicitly. It will work as fine.

 

[Stefan Hoffmann]

hi,

I am new to vba am and encountering some issues when attempting to run a sql
statement. I am having issues with Single Quotes and Double quotes causing
errors. If the values contain any single or double quotes I get an error.
See code below.

strSQL = "insert into newDB values ('" _
& rs.Fields(0).Value & "', '" &
rs.Fields(14).Value & "' )" '
DoCmd.RunSQL strSQL

You need the quotes when inserting strings only. And in this case you
have to consider the case that your input strings may contain quotes:

"INSERT INTO table VALUES('" + Input + "')"

will result in an erroneous statment, if Input contains single quotes, e.g.

Input = "D'oh!"

will result in this SQL statement

"INSERT INTO table VALUES('D'oh!')"

As you may see, there is a single quote too much in the statement.
To aviod this case, escape the single quotes:

"INSERT INTO table VALUES('" + Replace(Input, "'", "''" + "')"

will give us

"INSERT INTO table VALUES('D''oh!')"

which is a correct statement.


So use the Replace() on all your source fields.


mfG
--> stefan <--


p.s. VBA.Strings.Replace() is available in Access >= 2000

 

[bacadman]

Stefan,

I hate to sound totally ignorant, but do I need to use the replace on each
of the thirteen values?

Brian

 

[bacadman]

Stephan,

I got it to work. Thanks a bunch.

Brian

 

[Stefan Hoffmann]

hi,

I hate to sound totally ignorant, but do I need to use the replace on each
of the thirteen values?

Use a user function instead:

Public Function QuotedString(AString As String) As String

'VBA.Strings.Replace() requires Access>=2000
QuotedString = "'" & Replace(AString, "'", "''") & "'"

End Function

e.g.

SQL = "INSERT INTO table VALUES(" + QuotedString(Input) + ")"


mfG
--> stefan <--

 

[Sreedhar]

Hi,

Sorry, I didn't understand your question completely. You got it to work, and
all is well.