Embedded quotes in SQL statements

This month, Doug Steele passes on feedback he’s received from readers about past columns. Along the way, he shows how different priorities cause different programmers to come up with different solutions for the same problem.

See the rest of the questions: Access Answers: We Get Letters

One of the topics in the June column was a discussion of how to handle embedded quotes in SQL statements…

Adrian Murphy pointed out, quite correctly, that I neglected to mention that Parameter queries are another approach to solving the problem of having embedded quotes in the values being used in SQL statements. He sent along the following code sample:

Sub ParameterQueryAvoidingCharProblems()

Dim qdf As QueryDef

Dim rs As Recordset

Dim sSQL As String

Dim sText As String

sText = "Peter's ""Sweatshop"""

sSQL = "PARAMETERS [PAR1] TEXT; " & _

"SELECT * FROM TABLE1 " & _

"WHERE COMMENT=[PAR1]"

Set qdf = CurrentDb.CreateQueryDef("", sSQL)

qdf.Parameters("[PAR1]") = sText

Set rs = qdf.OpenRecordset

Do While Not rs.EOF

'etc

Loop

rs.Close

Set rs = Nothing

Set qdf = Nothing

End Sub

If you want to be able to use wildcards, change the equal sign to LIKE, and include the wildcard character(s) in the string you pass:

Sub ParameterQueryAvoidingCharProblems()

Dim qdf As QueryDef

Dim rs As Recordset

Dim sSQL As String

Dim sText As String

sText = "*ter's ""Swe*"

sSQL = "PARAMETERS [PAR1] TEXT; " & _

"SELECT * FROM TABLE1 " & _

"WHERE COMMENT LIKE [PAR1]"

Set qdf = CurrentDb.CreateQueryDef("", sSQL)

qdf.Parameters("[PAR1]") = sText

Set rs = qdf.OpenRecordset

Do While Not rs.EOF

'etc

Loop

rs.Close

Set rs = Nothing

Set qdf = Nothing

End Sub

As you can see, when you’ve defined a parameter of type Text (named PAR1 in the example), you can simply assign the string value, quotes and all, to the parameter and run your query, without having to worry about using a custom function to “adjust” the quotes.

Of course, it won’t work in Filters, which was what I used in the sample form in the database that accompanied the June column, but it’s certainly worth remembering in many situations. Thanks for the reminder, Adrian.

 

Your download file is called Steele_AA200410.accdb

 

Other Interesting Pages
 
Taming the Treeview Control
Everything About Using Parameters from Code
Is The Query Compiled?

About Doug Steele

Doug Steele has worked with databases-both mainframe and PC-for many years with a major international oil company. In a previous life, he taught introductory computer programming at the University of Waterloo. Microsoft has recognized him as an Access MVP for his contributions to the user community over the years.

Doug is the author of Access Solutions with Arvin Meyer.

This entry was posted in VBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.