apostrophe in text field causing unexpected error

  • Thread starter Thread starter Richard Hollenbeck
  • Start date Start date
R

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
 
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?
Click to expand...

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
 
Back
Top