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:

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:

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?

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.