Why I Prefer Double-Double Quotes ('') Instead of a Single Quote (') in Microsoft Access

In this Microsoft Access tutorial, I'm going to explain why I prefer using double-double quotes instead of single quotes in my criteria for strings.

ERRATA
The first line inside the DDQ function should be:
If IsNull(S) Then

LEARN MORE:

PRE-REQUISITES:

LINKS:

RECOMMENDED COURSES:

BECOME A MEMBER:

FREE TEMPLATE DOWNLOADS:

ADDITIONAL RESOURCES:

KEYWORDS:
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, DDQ, SQ, Why use single and double quotes in statements, difference between single and double quotes in SQL, When to Use Quotation Marks In MS Access, Quotation marks within quotes

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[mirchimasala]

Dear Richard .you are only and one hope for access developers....may you live long...keep it up legend Richard ...love from pakistan

[דודקלדרון-כפ]

As usual Rick, so clear explanations... I've learned so much from your videos.. You are truly a master. In the past, i had no idea how to deal with that syntax and all i did, was trying all the possibilities to make this to work. But now, everything looks so easy..
Thanks a lot!

[Shadow.Dragon]

The "mystery" of the DDQ solved! Thanks for the great explanation on how Access treats single and double quotes!

[AlejandroJuarez-jeen]

10:01. Hi, Richard, I was expecting to see "If IsNull(S) Then" instead of "If IsNull(DDQ) Then"...

[ryanw]

Don't you want IsNull(S) in your DDQ function? I knew this would be related to Apostrophes but I think Chr(34) & Replace(s, "'", "''") & chr(34) still works in those cases. (for my Qt function)

[mikewashington]

Using double double quotes always gives me a headache just like time travel examples.

[navisalomi]

Thanks Richard for stating your reasons. There is a workaround to escape the single quote character.
Infact, I wrote a function to put the single quotes to clear the headaches all together.
Anyone may try it to see. The function assumes the string would not contain double quotes.

Copy and paste function to a module to see the single quotes and escapes clearly. The function can be enhanced to handle double quote. I just think there very slim chances anyone would come across DDQ in a string.

Function as String
If IsNull(textValue) Then Exit Function
replace_escape_quotes= "'" & Replace(textValue, "'", "''") & "'"
End Function


With this function all you need to do is call the custom function inside the Dlookup
=DLookUp("[CreditLimit]", "[CustomerT]", "[LastName] = " &

The custom function removes the whole headache altogether. I tested for O'Brien and it worked perfectly.
I'm very comfortable with DDQ, but single quotes are more readable and modern programming languages like Python makes it even easier to use.