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.