The previous post discussed how single quotes (like apostrophes) can confuse SQL code and cause syntax errors. Single quote errors are perhaps more common (emphasis on "perhaps") but they do not hold an exclusive right to the Heartache and Discontent trophy. Double quotes appear in text strings, as well, and though the issues they cause are similar, they deserve separate attention.
DOUBLE QUOTES IN TEXT STRINGS
For this second example, suppose you have a bit of code that includes text from a Comments field. Because single quotes are common, especially in comments where contractions (don't or isn't) and possessive nouns (Fred's or customer's) are used, you may choose to wrap such text strings in double quotes. MS Access allows double quotes in SQL. (SQL Server... not so much.)
strSQL = "SELECT * FROM tblCustomers WHERE Comments= " & """" & me.txtComments & """"
First of all, I typically create a public constant in my declarations module for the entire project that includes the following:
Const Q As String = """"
Thus I can rewrite my code this way:
strSQL = "SELECT * FROM tblCustomers WHERE LName = " & q & me.txtComments & q
Let's further suppose that the comment includes the following:
The customer quoted Tom as being "a top-notch salesman"
Now we have a similar issue as in the last post. The resultant SQL code has an early termination of the quote and then it has a few incorrect command words and then another opening quote without a closing quote. We've got more errors than a little league game.
The FixQuotes() function, used in conjunction with the Quote() function, can help here, too.
The FixQuotes() function escapes the single double quote using four double quotes. SQL sees the four double quotes as an actual double quote (again, NOT the same as two single quotes) instead of the termination of a quote string.
Thus we can rewrite our code to look like this:
strSQL = "SELECT * FROM tblCustomers WHERE Comments = Quote(me.txtComments, true)
Note the inclusion of the optional boolean argument. This tells Quote (and, in turn, FixQuotes) to replace double quotes instead of single quotes.
I hope this helps! Happy coding...