Results 1 to 5 of 5
  1. #1
    dpindy
    Guest

    Using Like with apostrophe (ACC2000 SR2)

    I have an ACC2000 ADP frontending a SQL7 database.

    I'm trying to populate a list box with names that contain an apostrophe. The following code works in SQL server, but it gives an error (invalid column name)when I enter it in the RowSource property in the form.

    SELECT lname, fname FROM Respondent WHERE lname Like "o'neil%"

    Any ideas on how to get around this?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Like with apostrophe (ACC2000 SR2)

    Bound forms still use the Jet query engine, and Jet doesn't recognize the % sign as a wildcard in these circumstances. The % and _ wildcards are only available with Jet 4.x through the Jet OLEDB provider. Change it to an asterisk and see if that works.
    Charlotte

  3. #3
    dpindy
    Guest

    Re: Using Like with apostrophe (ACC2000 SR2)

    changing the % to * gives the same error

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Like with apostrophe (ACC2000 SR2)

    Since the message says invalid column name, it may have nothing to do with the wildcard. Take a look at this knowledgebase article: <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q156/5/01.asp>QUOTED_IDENTIFIER and Strings with Single Quotation Marks [Q156501]</A>. it may apply in your case. The sample it uses is
    <pre> select * from authors where au_lname = 'O''Brien'</pre>

    Note that there are two single quotes between the O and the B.
    Charlotte

  5. #5
    dpindy
    Guest

    Re: Using Like with apostrophe (ACC2000 SR2)

    Thanks for the suggestion ... Unfortunately I couldn't ever get it to accept the double quotes as a text delimiter ... I got around the problem by doing a search and replace of the lname when a single quote is present to make the WHERE clause:

    WHERE lname Like 'o_neil%'

    This populates the list box adequately...

    Thanks again for your suggestions.

Posting Permissions

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