Handling quotes in data entries

  • Thread starter Thread starter John Kaurloto
  • Start date Start date
J

John Kaurloto

To the group:

I have no doubt this has been asked before...
Could someone point me to information about handling quotes or apostrophes
in data.

Example:

rs.FindFirst "[txtName] = """ & Me![cboName] & """"

If txtName = Show 2004 "First in Georgia"
the line fails because of the quotes in the data entry


Any assistance is appreciated.

Thank you
 
You have the choice of using either single quotes (') or double quotes (")
as a delimiter. Since your string has double quotes in it, I'd advise using
single quotes as the delimiter:

rs.FindFirst "[txtName] = '" & Me![cboName] & "'"

Exagerated for clarity, that's:

rs.FindFirst "[txtName] = ' " & Me![cboName] & " ' "

However, that won't work if you have both single and double quotes in your
string, such as

Show 2004 "First in O'Darby"

In that case, you need to decide which one you want to use as the delimiter,
and then double it up in your statement. For example, if you want to use
single quotes as a delimiter, you'd use:

rs.FindFirst "[txtName] = '" & Replace(Me![cboName], "'", "''") & "'"

Again exagerated for clarity:

rs.FindFirst "[txtName] = ' " & Replace(Me![cboName], " ' ", " ' ' ") & " '
"

The doubled up characters will be interpretted correctly by the query.
 
John said:
To the group:

I have no doubt this has been asked before...
Could someone point me to information about handling quotes or apostrophes
in data.

Example:

rs.FindFirst "[txtName] = """ & Me![cboName] & """"

If txtName = Show 2004 "First in Georgia"
the line fails because of the quotes in the data entry
Click to expand...


The rule is that quotes within quotes have to be doubled up.

An easy way to achieve this is to use the Replace function:

rs.FindFirst "[txtName] = """ & Replace(Me![cboName], """",
"""""") & """"

In several contexts (but not VBA), you can use a single
quote (apostrophe) to enclose a string containing double
quotes and vice versa.

Even though your FindFirst is part of a VBA procedure, the
condition string is executed by DAO, so,
***if there is no chance that a single
quote could be entered by a user***
you could use this instead:

rs.FindFirst "[txtName] = '" & Me![cboName] & "'"
 
Back
Top