Sunday, September 23, 2012 12:17 AM
How can I select all names from a field in a SQL Server table that includes an "'" in the field? The field is named [Patient ID] and the table is named "tblPats". Thanks!
Sunday, September 23, 2012 1:02 AM
If you actually mean an Access Query using ODBC Linked Tables sourced from SQL Server then you you can use double-quotes as Text/String delimiter and therefore, you can use an SQL like:
SELECT tblPats.[Patient ID], tblPats.FirstName, tblPats.LastName
WHERE tblPats.LastName Like "*'*"
* is the wildcard for any sequence of characters.
Sunday, September 23, 2012 1:44 PM
However, if you're running the query as a pass-through (so that it runs directly on the server), or if you've set ANSI 92 compatibility, or if you're using ADO, the wild-card character is %:
SELECT tblPats.[Patient ID], tblPats.FirstName, tblPats.LastName FROM tblPats WHERE tblPats.LastName Like "%'%"
Actually, if this is a pass-through query to SQL Server, the text delimiter is a single quote, not a double quote, so the SQL would be:
SELECT tblPats.[Patient ID], tblPats.FirstName, tblPats.LastName FROM tblPats WHERE tblPats.LastName Like '%''%'
(note the two single quotes in a row to handle the embedding of a single quote within single quotes)
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
- Proposed As Answer by Yoyo JiangMicrosoft Contingent Staff, Moderator Monday, September 24, 2012 6:20 AM
Wednesday, September 26, 2012 5:25 AM
Wednesday, September 26, 2012 5:26 AM
This is very helpful. Thanks.