Solved Understanding the Double Quotes

[Weekleyba]

I am looking for a break down of how the double quotes work in the following from Allen Brown http://allenbrowne.com/casu-17.html :
If you wanted to look up the city for the CompanyName in your form, you need to close the quote and concatenate that name into the string:
=DLookup("City", "Customers", "CompanyName = """ & [CompanyName] & """")

I'm looking for a explaination of criteria portion and specifically a break down of the usage of quotes.
Here is what I think it is:

The first double quote ahead of CompanyName is needed for this functions criteria section.
Then you have the double double quotes after the equals sign, giving an empty string.
Then you gave a double quote ending the string.
Then at the end, you have a double quote that begins a string
Then a double double quote, giving an empty string.
A finally, a double quote ending the string.


This is probably not right, but can someone break it down like this, and explain it to me.
Thank you.

 

[moke123]

"CompanyName = """ & [CompanyName] & """"
the first and last quotes show that the full string is a string

"CompanyName = """ & [CompanyName] & """"
These quotes are for the concatenation for the company name

"CompanyName = """ & [CompanyName] & """"
the double quotes inside are escaped (doubled up) so that the string resolves to having interior quotes or string delimiters.
like: CompanyName = "Acme"

you could also use single quotes
"CompanyName = '" & [CompanyName] & "'"
However if you use single quotes you will get an error if the word has an apostrophe in it like O'Brien

 

[Weekleyba]

Thank You!
You made it clear to me now.
I’m going to print this out and tape it to my computer.
Thanks again.

 

[Uncle Gizmo]

the string resolves to having interior quotes or string delimiters.

Because of the difficulty in reading code containing these double quotes, I generally replace them with this code:- Chr(34) ....

Example on my Blog here:-

INSERT INTO SQL Statement – Structured Guide - Nifty Access

Last updated: 2025-02-21 20:07:31 INSERT INTO SQL StatementThe INSERT INTO SQL Statement is known for its complexity. Many struggle with syntax errors that are often misleading. A structured approach makes handling SQL statements easier.Divide & Conquer the INSERT INTOBreaking down SQL...

www.niftyaccess.com

 

[Uncle Gizmo]

Referring to my blog, this line of code represents the different types of variables that can be passed in:-

strSQL2 = "Values(" & txtFld1 & ", " & txtFld2 & ", " & txtFld3 & ", " & txtFld4 & ")"

The variable delimiters are added earlier, added at the beginning of the routine, leaving this line of code in a "simple" "repeatable" format.

 

[gemma-the-husky]

much easier. (IMO) I use chr(34), which is the ascii code for ", and then you don't need to include them twice.

=DLookup("City", "Customers", "CompanyName = " & chr(34) & CompanyName & chr(34))

 

[Pat Hartman]

I'm with the others except that i don't like using chr(34) so I create my own constant.

Public Const QUOTE = """"

That translates to

=DLookup("City", "Customers", "CompanyName = " & QUOTE & CompanyName & QUOTE)

 

[Weekleyba]

Thanks guys for the additional ways to accomplish this. Very appreciated. #tricksofthetrade#

 

[gemma-the-husky]

I'm with the others except that i don't like using chr(34) so I create my own constant.

Public Const QUOTE = """"

That translates to

=DLookup("City", "Customers", "CompanyName = " & QUOTE & CompanyName & QUOTE)

And that's definitely one occasion a global constant is fully justfied!

 

[mikenyby]

And that's definitely one occasion a global constant is fully justfied!

@Pat Hartman

Sorry to bring this up on a 3-year old thread, but I love this idea and want to implement it but I keep getting the "Constants, fixed-length strings, arrays, user-defined types, and declare statements not allowed as Public members of object modules" error message. According to MS, this should work when using Option Explicit. Any ideas? This is my code:

Option Compare Database
Option Explicit
Public Const QUOTE = """"

 

[mikenyby]

@Pat Hartman

Sorry to bring this up on a 3-year old thread, but I love this idea and want to implement it but I keep getting the "Constants, fixed-length strings, arrays, user-defined types, and declare statements not allowed as Public members of object modules" error message. According to MS, this should work when using Option Explicit. Any ideas? This is my code:

Option Compare Database
Option Explicit
Public Const QUOTE = """"

Never mind! Solved it, made a db-wide module.

 

[Pat Hartman]

That's the answer