apostrophe in text field causing unexpected error

[Richard Hollenbeck]

I have a series of DAO.Recordsets querying different fields and concatinates
the results into a string variable. That string variable goes into a text
box. So it goes something like this:

lname & ", " & fname & vbcrlf & address1 & vbcrlf .... etc., etc., etc.

But if the name in the lname field contains an apostrophe, such as, "Jack's
Liquor" or "Jill's Nursery," I get a big error. Error number 3075 - missing
operator in expression. It sees the apostrophe and
wiggs-out. Is there a way I can trap that error and programatically fix it
on the fly? Or do I have to prohibit apostrophes in the field? Some
programming languages make use of a backslash to get the program to ignore
the next character. Does VBA have anything like that? If not, what is the
best solution?

Thanks.

Rich Hollenbeck

 

[John Vinson]

But if the name in the lname field contains an apostrophe, such as, "Jack's
Liquor" or "Jill's Nursery," I get a big error. Error number 3075 - missing
operator in expression. It sees the apostrophe and
wiggs-out. Is there a way I can trap that error and programatically fix it
on the fly? Or do I have to prohibit apostrophes in the field? Some
programming languages make use of a backslash to get the program to ignore
the next character. Does VBA have anything like that? If not, what is the
best solution?

Two suggestions:

- If you can be sure you'll never have a " character in the string,
use " instead of ' to delimit the text string in the query.

- Or, you can insert two consecutive ' characters wherever you want a
' in the string: use Replace([lname], "'", "''") instead of just
[lname].

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps