SQL strings with quotation marks or apostrophes trouble!

[David G.]

I'm trying to write an SQL statement where one or more field entries
include strings with quotations mark.

for instance:
20" Tube

The quotation mark translates to "inches", and needs to be stored in
the field.

or in another case
Support "T"-Beam

Here's short (very abbreviated) example of what I'm trying to do in
VBA (which doesn't work, by the way)....

Sub MySub()
Dim strField1 as string
Dim strField2 as String
Dim QUOTE as String

QUOTE="""
' comment triple quotations to improve code readability.
strField1=xmlFile.ChildNodes(0).Text
' comment: Text from node is 'Support "T"-Beam' (w/o single quote
marks)
if InStr(1,strField1,Chr$(34)) Then
strField=Replace(strField1,Chr$(34), QUOTE)

strField2=xmlFile.ChildNodes(1).text
' comment: Nothing special about this text

strSQL="INSERT INTO " & strTablename & " (FIELD1, FIELD2) " & _
"Values (" & QUOTE & strField2 & ", " & QUOTE & strField1 & ");"

currentdb.execute strSQL,dbFailOnError

End Sub

Any insight would be greatly appreciated.
THANKS!
David G.

 

[Dirk Goldgar]

Thanks. I will try this.

I'm not sure about your Replace statement. Currently, Chr$(34) is
found as a single occurrence in the string. Will searching for Q
(since it is four Chr$(34)'s) find a lone double-quote?

The constant Q is actually only one double-quote character, exactly the same
as Chr$(34). Its declaration is made with four quotes in a row: one quote
on either end to delimit the string literal, and two successive quotes
between them, which will be interpreted as only one quote character --
remember what I said about doubling up quotes inside a quoted string? You
can check this out in the Immediate Window:

? """"
"

 

[David G.]

Thanks for your comments. After researching the Parameter issue, I
want to pursue this method; however, My SQL statement is a ""INSERT
INTO" with no criteria. (I'm importing a bunch of info from another
source.)

I can't find any examples so far, so I would like to ask if I'm even
close:
SQL="PARAMETERS Field1 TEXT, Field2 TEXT; " & _
"INSERT INTO" & tblName & " (Field1Name, Field2Name) VALUES " & _
"([Field1],[Field2];"

I don't have the database with me to test this out, and lacking any
examples I thought your response might benefit others.

Regards,

Dirk already gave you a good way to get it working in VBA.

For queries, I usually like to use parameter queries and thus avoid the
messy doubling up/escaping and thus simplify the VBA.

The best part about parameter queries is that it allows you to not even
worry or care how the string may be inputed. It could be a problematic
string like this:

Mike's & Al's "Good" Cars

and it'd just work.


Example SQL:

PARAMETERS MyText TEXT;
SELECT *
FROM aTable
WHERE someCol = [MyText];


Example VBA #1:

With CurrentDb.QueryDefs("MyQuery")
.Parameters("MyText") = Me.MyTextbox
Set Me.MySubform.Form.Recordset = .OpenRecordset
End With


Example VBA #2:

Dim this As String

this = "Mike's & Al's ""Good"" Cars"

With CurrentDb.QueryDefs("MyQuery")
.Parameters("MyText") = this
Set Me.MySubform.Form.Recordset = .OpenRecordset
End With


HTH.

I'm trying to write an SQL statement where one or more field entries
include strings with quotations mark.

for instance:
20" Tube

The quotation mark translates to "inches", and needs to be stored in
the field.

or in another case
Support "T"-Beam

Here's short (very abbreviated) example of what I'm trying to do in
VBA (which doesn't work, by the way)....

Sub MySub()
Dim strField1 as string
Dim strField2 as String
Dim QUOTE as String

QUOTE="""
' comment triple quotations to improve code readability.
strField1=xmlFile.ChildNodes(0).Text
' comment: Text from node is 'Support "T"-Beam' (w/o single quote
marks)
if InStr(1,strField1,Chr$(34)) Then
strField=Replace(strField1,Chr$(34), QUOTE)

strField2=xmlFile.ChildNodes(1).text
' comment: Nothing special about this text

strSQL="INSERT INTO " & strTablename & " (FIELD1, FIELD2) " & _
"Values (" & QUOTE & strField2 & ", " & QUOTE & strField1 & ");"

currentdb.execute strSQL,dbFailOnError

End Sub

Any insight would be greatly appreciated.
THANKS!
David G.

THANKS!
David G.