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

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?

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";

...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

Escape the apostrophe in O'Neal by writing O''Neal (two apostrophes).

How about more simply: Select * from tblStudents where [name] = replace(YourName,"'","''")