SQL to Query text in access with an apostrophe in it
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.
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";
Escape the apostrophe in O'Neal
by writing O''Neal
(two apostrophes).
...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