Qouation Characters in Access

I've got an access database which stores peoples names, and we have some forms that allow people to search on these names.

I'm having a problem searching on names like "O'Donnell" which has a quoation mark (chr(39)) in it. because it get's passed to access like this "Select * from table1 where surname ='O'Donnel'", it breaks the string after the letter.

Does anyone know a way around this. Many thanks.

Hello Auld Nick II,

If I had your problem, the name "O'Donnell" should be saved in the table by "O~Donnell" or an other never used character.

Searching:

before selecting you have to rename the surname!
Select * from table1 where surname ='O~Donnel'


Nice regards,

Michelle.

Thanks for that Michelle, but I've actually found another way using the visual basic replace function. This put's further quotes round the ' which allows me to stored the data as O'Donnell and search it as O'Donnell.

Thanks for replying though.

Kind Regards,

AuldNick

All you have to do is use double quotes instead of single quotes in your SQL string.

"Select * from table1 where surname ="""O'Donnel""""

You have to use 3 double quotes to have it recognize the one double quote. (""" = ")
At the end you have 4 because of your end quote for the quote before Select.


If I remember correctly....

Thanks Arkhos, but that won't work for me because I'm building up the query in vb. Will most probably be useful for future reference though.

Thanks

Hello Auld Nick II again,

Can you pls explain the way you have done it? I don't understand the way you described.

Nice regards,

Michelle.