Navigation:  DAO > Embedded quotes in SQL statements - DAO Code

Doug Steele

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 410STEELE.ZIP in the file SA2004-10down.zip

This is found in the Gold Collection on this page

 

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