Qouation Characters in Access

[Auld Nick II]

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.

[michelle]

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.

[Auld Nick II]

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

[Arkhos]

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....

[Auld Nick II]

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

[michelle]

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.