quote in a string : query problem 2

[tyris]

hi all,
i'm a begginer in SQL so can someone help me ?

i have this problem :
i have a query that searches all contacts that have a name that begins with a specified letter.

if the user looks for all the contacts that begin with the "tr" string :
SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'tr%' ORDER BY ContactLName;

here is no problem

the problem is when the contact has a name like o'conneil for exemple ... because it gives something like :

if the user looks for all the contacts that begin with the "o'" string:
SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'o'%' ORDER BY ContactLName;

you see the problem ? the quote in the contact name is a problem because under VB in the query the quote is used for a string delimitation ...
but the contact name is entered like " o'conneil " in the database ...
what can i do ?


ps : sorry for my bad english
Best regards,
Elise

 

[TheBigD]

All you have to do is add another apostrophy.

replace(string,"'","''&quot

example: search for "O''Connell"

 

[tyris]

thanks that works Best regards,
Elise