Single Quote in where clause causing problems.

Hello: i have a simple select statement and i ran accross a situation where it's erroring out because of a single quote that a user assigned to the customer name:

select * from tblCustomer where customername='PLATO'S'

here's my sql:
select * from tblCustomer where customername='" & sCustomerName & '"

I would like to allow the user to insert customer's w/ the single quote.
Is there a way to change my syntex so that I can allow for them?

e.g. i can use instr to determine if the customer name has a single quote and then if it does, I can replace it with the correct syntax needed for the sql?

thanks for your help,
Proctor

There are various ways to solve it, the best of them is to avoid that issue (and others) by using Parameters.

For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of this forum).