Imbedded Single Quote

[cwh2724 via AccessMonster.com]

I have a query where I am trying to select data based on a manager's name.
One particular manager has a single quote at the end of her first name
(Renee'). Since the database that I am querying contains the name with the
quote in it, how do I go about selecting the information for that manager?

I am using Access 97. Currently in my SQL statement I have "WHERE ManagerName
= ' " & UnitMgr & " ';" and UnitMgr stores the value of the manager that was
selected on the Unit Manager form. The selection on the Unit Manager form is
built from the detail level data for a specific date range.

In reviewing other threads on this site, I see where I can use REPLACE but it
does not appear that is valid for Access 97. Are there any other
alternatives? If I use something like Replace on the UnitMgr field, how will
it find matching values from the MangerName field?

Thank you in advance for any assistance you can provide.

 

[Ron2006]

Instead of

WHERE ManagerName = ' " & UnitMgr & " ';"

use

WHERE ManagerName = """ & UnitMgr & "";"




using a set of double quotes to get the single quote saved as part of
the SQL

 

[tkelley via AccessMonster.com]

Ron's method is correct ... but I thought I'd share another tip that I have
started using because I don't like having to squint. I started using the
ascii code several years ago, and have kept with it ever since:

WHERE ManagerName = chr(34) & UnitMgr & chr(34)

chr(34) is the ascii equivalent of the double quote, and is immediately
recognizable as to what it is and what it does.

 

[cwh2724 via AccessMonster.com]

Thank you both for your assistance. It works!