ACCESS FEED: RE: [MS_AccessPros] Using double quotes (") in SQL for a query

Rabu, 06 Agustus 2014

RE: [MS_AccessPros] Using double quotes (") in SQL for a query

Hi Robin

To express a delimited literal string which contains embedded instances of the delimiter, the embedded instances must be “escaped”. One method of escaping delimiters (and the method used in both SQL and VBA) is to “pad” the delimiter by doubling it.

Let’s say our string literal is:
Billy "Bogface" O'Brien

To delimit this with single-quotes, we need to double the apostrophe:
'Billy "Bogface" O''Brien'

Or to delimit with double-quotes, we double the double-quotes:
"Billy ""Bogface"" O'Brien"

If you don’t know the content of the string because it is stored in a variable or a field, then you can use the Replace function to replace each instance of the delimiter with two of them. For example:

MyRecordset.FindFirst "FullName=" & Chr(34) & Replace( MyVariable, Chr(34), Chr(34) & Chr(34) ) & Chr(34)

In your case, the string you are constructing is used only for display, not as an element of the SQL string (for example a WHERE clause), so you don’t actually need either delimiters or escaping – just the quotes around the [Message] value:

Entry: [Nayme] & " said " & Chr(34) & [Message] & Chr(34)

I hope that’s all clear as mud :)

Best wishes,

Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, 7 August 2014 16:38
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Using double quotes (") in SQL for a query

I need to concatenate a field [message] as part of a new field called [entry].

This is my failed attempt:

Entry: [Nayme] & " said " & Chr$(34)" " & [Message] & "Chr$(34); "

The intention is that the field should show - John Smith said "It's a wonderful world".

The Microsoft help pages give this advice:

The ANSI representation for double quotation marks is Chr$(34); you could assign this value to a string variable called strQuote. You could then construct the criteria argument

I really do not understand the various pages that I have followed to attempt to understand.

Is there a straightforward answer?

Many thanks,

Robin Chapple