SQL to Query text in access with an apostrophe in it

sql ms-access
Kevin picture Kevin · Jul 20, 2011 · Viewed 117.7k times · Source

Please help me with this because I cannot seem to get it right

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'

because of the apostrophe in the name.

How do I overcome this.

Thank you in advance

Answer

Alex K. picture Alex K. · Jul 20, 2011

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.

Related questions

How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More
How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More