Apostrophe / single quote / Chr$(39) in a name

[John Wyman]

Common problem: I enter a last name (or possibly other text) into a LastName
field on a form which is based on a query but finally stores the last name in
the LastName field in the underlying table. If the name contains a ' (e.g.
O'Brien) the text get stored in the table with the ' in place. Then when
List Boxes or other objects try to use that field, the ' gets in the way and
causes a failure to find that record.
Without writing too much code (I'm weak in this area), how can I work around
this problem? Of course I could use " instead of the ' but this seems like
an ugly solution.

 

[John W. Vinson]

Common problem: I enter a last name (or possibly other text) into a LastName
field on a form which is based on a query but finally stores the last name in
the LastName field in the underlying table. If the name contains a ' (e.g.
O'Brien) the text get stored in the table with the ' in place. Then when
List Boxes or other objects try to use that field, the ' gets in the way and
causes a failure to find that record.
Without writing too much code (I'm weak in this area), how can I work around
this problem? Of course I could use " instead of the ' but this seems like
an ugly solution.

The ' doesn't get in the way unless your query tries to delimit a criterion
with ' marks. That's not essential; you can use " as a delimiter instead.
Could you post a specific example of it "getting in the way"?

 

[John Wyman]

On the form, a list box listing all the names in the table, will fail to
select the record with the offending name. The name will be listed, but when
clicked (selected) will not select that record because the ' causes the
underlying code to fail.

 

[John W. Vinson]

On the form, a list box listing all the names in the table, will fail to
select the record with the offending name. The name will be listed, but when
clicked (selected) will not select that record because the ' causes the
underlying code to fail.

Post the code. That's fixable.

It sounds like you're searching *ON THE NAME* which is probably a bad idea -
names are *not* unique. Normally the listbox/combo box would display a name
but have a numeric ID as its bound column, and use THAT to search.

 

[John Wyman]

Yup, yer right. Why didn't I think of that? Thanks John.