SQL to Query text in access with an apostrophe in it

Question

I am trying to query a name  Daniel O Neal  in column names tblStudents in an Access database  however Access reports a syntax error with the statement  Select   from tblStudents where name like  Daniel O Neal   due to the apostrophe in the name  How do I overcome this

User · Answer

Escape the apostrophe in O Neal by writing O  Neal  two apostrophes

User · Answer

When you include a string literal in a query  you can enclose the string in either single or double quotes  Access  database engine will accept either   So double quotes will avoid the problem with a string which contains a single quote   SELECT   FROM tblStudents WHERE  name  Like  Daniel O Neal     If you want to keep the single quotes around your string  you can double up the single quote within it  as mentioned in other answers   SELECT   FROM tblStudents WHERE  name  Like  Daniel O  Neal     Notice the square brackets surrounding name   I used the brackets to lessen the chance of confusing the database engine because name is a reserved word   It s not clear why you re using the Like comparison in your query   Based on what you ve shown  this should work instead   SELECT   FROM tblStudents WHERE  name     Daniel O Neal

User · Answer

How about more simply  Select   from tblStudents where  name    replace YourName

User · Answer

better is declare the name as varible  and ask before if thereis a apostrophe in the string   e g    DIM YourName  string  YourName       Daniel O Neal     If InStr YourName       Then       SELECT   FROM tblStudents WHERE  name   Like     Your Name          else       SELECT   FROM tblStudents WHERE  name  Like    Your Name               endif

User · Answer

You escape   by doubling it  so   Select   from tblStudents where name like  Daniel O  Neal     Note that if you re accepting  Daniel O Neal  from user input  the broken quotation is a serious security issue  You should always sanitize the string or use parametrized queries

[sql] SQL to Query text in access with an apostrophe in it

Examples related to sql

Examples related to ms-access