Hi,

I am using the following query

"select trim(name),trim(page) from [MEDICAL FACILITIES] where [name] is not null and [name] like '" & Trim(Text1.Text) & "%' order by [name]"

to select all the records that matches the name entered in the textbox-Text1.

The query works fine except when the text entered in the textbox has an " ' ". For example, when i search for "Woman's Hospital". The error i get is

Syntax error (missing operator) in query expression

Someone please tell me how I can solve this problem.

Thanks,
Suresh.

Hi,

you should parse the textbox.text and duplicate any single " ' " . the sql-server will handle the " ' " as a literal-terminator, but " '' " (i.e. 2x ' ) will be handle as a literal.

HI,


Thanks for your reply. But if I duplicate " ' " with double " " "(or any other character) then the text entered will not match with the data in the MSAccess database. So i will not get the correct result.

There should be some way to solve this problem.

Hi,


the ( 2x ' ) will be interpreted as ONE literal '

so the text should match - give it a try, and be sure not to replace with " (one char) instead of ' plus ' (two chars).

maybe the following selects will help

-- ascii values used with char-function
select 'Double' = char(34), 'Single' = char(39)
-- direct literal , uses 2x char(39) when entered
select 'Woman''s Hospital'
-- calculated literal
select 'Woman'+char(39)+'s Hospital'

Hi,


Thank you very much for the solution. It works perfect.