How to write SQL with string containing an apostrophe

David_W_R
David_W_R used Ask the Experts™
on
This statment works fine until the 'SelectedCompany' contains an apostrophe

    SQLstatement = "SELECT * " _
    & "FROM tbContactsNonClients " _
    & "WHERE Company = '" & SelectedCompany & "';"
 
How do I accomodate a string with an apostrophe since I'm using the 'single quotes' to avoid conflict among the "double quotes"?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Just replace single quotes with two single quotes to get it work like

'a''abc'  to get the value a'abc
Top Expert 2016
Commented:
you can do this

 SQLstatement = "SELECT * " _
    & "FROM tbContactsNonClients " _
    & "WHERE Company = " & chr(34) & SelectedCompany & chr(34)

Author

Commented:
rrjegan17, I tried replacing single quotes with 2 single quotes a couple of ways, but couldn't get it to work.   Did you possibly mean that the entry in the table should be modified?  That wouldn't be an acceptable solution, and I don't think that's what you meant, rrjegan17.   I'd like to see a modification of my code to show how this has to be written.

I can only accept the ascii code solution now, since it worked the first time.  Thanks very much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial