Lookup with single quote

[Alain]

Hi,

Access XP

I use a combo box to retrieve records in a table. Everything works OK
except when searched string (Name) contains a single quote (L'Heureux).

Name is found in ComboBox strings but I get an Execution error message
#3077: Syntax error, Missing operator in expression (This is translated
from French).

How should I proceed to find such records?

Thanks.

Alain

 

[fredg]

Hi,

Access XP

I use a combo box to retrieve records in a table. Everything works OK
except when searched string (Name) contains a single quote (L'Heureux).

Name is found in ComboBox strings but I get an Execution error message
#3077: Syntax error, Missing operator in expression (This is translated
from French).

How should I proceed to find such records?

Thanks.

Alain

Two things.
1) If the name of your field is really "Name" I would strongly suggest
you change it. "Name" is an Access reserved Key word and should not be
used as a field name. Read on of the following Microsoft KnowledgeBase
articles:
109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

2) Posting your code would have been helpful.
You're probably using something like:
"[SomeField] = '" & [ComboName] & "'"
as criteria to search for the name.
Change it to:
"[SomeField] = " & chr(34) & [ComboName] & chr(34)

 

[John Nurick]

Hi Alain,

If you examine the VBA code behind the combobox you'll find that at some
point it assembles a WHERE clause that looks somewhat like this:

"Name = '" & cboName & "'"

where cboName is the name of the combobox. You can see how it's
enclosing the value of the combobox in single quotes to produce e.g.
Name = 'Smith'
which is fine, or
Name = 'L'Heureux'
which isn't.

The simplest way to fix it is usually to replace the single quotes with
double quotes - though you have to double the double quotes when they're
inside a string literal, so you get
"Name = """ & cboName & """"
which produces
Name = "L'Heureux"

If this doesn't work, post back, pasting the relevant lines of code into
your message and giving details of your setup (what version of Access,
is the data in an mdb file, or if not what database engine you are
using).

 

[Alain]

Hi Fred,

Thanks for your input.

The name of the field is not Name, but data in the field refers to a
(French) name, which explains the occasional single quote.

Your suggestion works fine. Thanks.

Alain

fredg a écrit:

Alain wrote:

Hi,

Access XP

I use a combo box to retrieve records in a table. Everything works OK
except when searched string (Name) contains a single quote (L'Heureux).

Name is found in ComboBox strings but I get an Execution error message
#3077: Syntax error, Missing operator in expression (This is translated
from French).

How should I proceed to find such records?

Thanks.

Alain

Two things.
1) If the name of your field is really "Name" I would strongly suggest
you change it. "Name" is an Access reserved Key word and should not be
used as a field name. Read on of the following Microsoft KnowledgeBase
articles:
109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

2) Posting your code would have been helpful.
You're probably using something like:
"[SomeField] = '" & [ComboName] & "'"
as criteria to search for the name.
Change it to:
"[SomeField] = " & chr(34) & [ComboName] & chr(34)

 

[Alain]

Hi John,

Thanks for your suggestion; it works fine.

Alain

John Nurick a écrit: