Handling Quotations

This month, Doug Steele shows one way to add shortcuts to your application, demonstrates how to handle embedded quotes in SQL statements, and provides a quick documenter to list the objects in your application.

See the rest of the Access Answers article on : Shortcuts, and What Have I Done Here

I’m building SQL strings in code, but I’m having problems handling text that includes quotation marks.

When you’re building your SQL strings dynamically, incorporating variables in the string can sometimes be problematic. Remember that what you need to do is enclose, in quotes, any text values in the SQL string. In other words, you need your SQL string to look something like this:


To use a variable rather than the constant, you need to explicitly add the quote marks, and concatenate the value of the variable (instead of the variable itself) into your SQL string. In other words, you need to write code like this, which puts double quotes around the value:

You can also write code like this that puts single quotes around the value:

It may be hard to make out the last part of the code: After the last ampersand, I have a double quote-single quote-double quote combination.

It’s also possible to create a constant cQuote and set it to “”””, so that you can use:

This eliminates some of the ugliness of the first example. Since Chr$(34) is the same as “, you can also use:

The problem with any of these approaches is that they can fail to work if strText contains quotes. For example, if you use the second approach, it’s going to fail if strText contains a single quote in it (such as O’Brien). Any of the other three approaches will work with O’Brien, but will fail if you’ve got a double quote in your string (such as The “Olde Sodde”). In other words, this will work:

It also depends on what database you’re talking to. If you’re writing SQL to go against SQL Server, for example, often you must use single quotes as a delimiter.

Since you don’t always know what your users are going to be keying in for search purposes, you need something a little more flexible than just concatenating single or double quotes. What I find easiest to do is to create a function that corrects the quotes for you. With such a function, rather than having to worry about counting quotes, you can simply do something like this:

The “trick” is to ensure that whatever character you’re using to delimit your text strings (either ‘ or “), you must double any occurrence of that character within your string. A function such as the following will suffice:

The Replace function in the preceding code only works in Access 2000 and higher. If you’re using Access 97 or earlier, you’ll need to write your own equivalent function. The database in the accompanying Download includes such a function.

It’s easy to extend such a function to allow for its use with the Like function as well:

So what happens if strText contains O’Brien? You’d use my function like this to find any record whose Comment field ends in O’Brien:

If you want to find those records with O’Brien at the start of the Comment field, use this:

Finally, if you want to find O’Brien anywhere in the Comment field, call my function like this:

If you’re going to be going against a DBMS other than Jet, or if you’re using ADO, you’ll need to rewrite my function to use % instead of * as the wildcard. Enhancing the function to accept the wildcard character as a parameter is another way of accommodating this:

Here’s my new version of the function in action:

With this version of the function, you must specify True or False for both FrontWildcard and EndWildcard in order for this to work. This will succeed:

This will not:


Your download file is called   Steele_ShortcutsQuotesObjects.accdb


Or try Embedded quotes in SQL statements

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.