Quotation marks within quotes

        14 votes: *****      49,554 views      0 comments
by Allen Browne, 30 June 2006    (Access 95+)

Microsoft Access Tips for Casual Users

Provided by Allen Browne, June 2006.


Quotation marks within quotes

In Access, you use the double-quote character around literal text, such as the Control Source of a text box:
    ="This text is in quotes."

Often, you need quote marks inside quotes, e.g. when working with DLookup(). This article explains how.

Basics

You cannot just put quotes inside quotes like this:
    ="Here is a "word" in quotes"        Error!
Access reads as far as the quote before word, thinks that ends the string, and has no idea what to do with the remaining characters.

The convention is to double-up the quote character if it is embedded in a string:
    ="Here is a ""word"" in quotes"

It looks a bit odd at the end of a string, as the doubled-up quote character and the closing quote appear as 3 in a row:
    ="Here is a ""word"""

Summary:

Control Source property Result Explanation
="This is literal text."
This is literal text.
Literal text goes in quotes.
="Here is a "word" in quotes"

Error: The expression you entered contains invalid syntax

Access thinks the quote finishes before word, and does not know what to do with the remaining characters.
="Here is a ""word"" in quotes"
Here is a "word" in quotes
You must double-up the quote character inside quotes.
="Here is a ""word"""
Here is a "word"
The doubled-up quotes after word plus the closing quote gives you 3 in a row.

Expressions

Where this really matters is for expressions that involve quotes.

For example, in the Northwind database, you would look up the City in the Customers table where the CompanyName is "La maison d'Asie":
    =DLookup("City", "Customers", "CompanyName = ""La maison d'Asie""")

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] & """")

The 3-in-a-row you already recognise. The 4-in-a-row gives you just a closing quote after the company name. As literal text, it goes in quotes, which accounts for the opening and closing text. And what is in quotes is just the quote character - which must be doubled up since it is in quotes.

As explained in the article on DLookup(), the quote delimiters apply only to Text type fields.

The single-quote character can be used in some contexts for quotes within quotes. However, we do not recommend that approach: it fails as soon as a name contains an apostrophe (like the CompanyName example above.)


Home Index of tips Top

<< Back to tutorials index




Please Note: This is a cached tutorial (reproduced with permission).
To view the tutorial at its original location click here.



Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent

Have your say - comment on this article.

What did you think of 'Quotation marks within quotes'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code
Please note: It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.   All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.


Site tools