Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    8

    Unanswered: Syntax error (missing operator) in query expression

    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.

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: Syntax error (missing operator) in query expression

    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.

  3. #3
    Join Date
    Jun 2003
    Posts
    8

    Re: Syntax error (missing operator) in query expression

    Originally posted by msieben
    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.

  4. #4
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: Syntax error (missing operator) in query expression

    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'

  5. #5
    Join Date
    Jun 2003
    Posts
    8

    Re: Syntax error (missing operator) in query expression

    Originally posted by msieben
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •