Re: Search Form

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 11:04:27 -0400

Wrap the control reference with the Replace function to double up the '
character (that allows ACCESS to see it as a single, embedded ' character in
the text string):

  strqry = strqry & "((ACCOUNT_ARCHIVE.ACCOUNT_NAME) LIKE '%" &
Replace(txtACCOUNT_NAME, "'", "''", 1, -1, vbTextCompare) & "%') AND " 

-- 
        Ken Snell
<MS ACCESS MVP>
"Brian" <anonymous@discussions.microsoft.com> wrote in message
news:c5ae01c489de$a4808e50$a601280a@phx.gbl...
> Hello.  I have a form that builds an SQL statement
> depending on what fields you type data into.  It all
> works fine until I search for a name with an apostrophe
> (') in it, such as "Papa John's".
>
> The code I have so far is below:
> If txtACCOUNT_NAME <> "" Then
>   strqry = strqry & "((ACCOUNT_ARCHIVE.ACCOUNT_NAME)
> LIKE '%" & txtACCOUNT_NAME & "%') AND "
>     End If
>
> This is just a section on the SQL statement that the form
> builds.  But how can I make it accept the apostrophe?
>
> After searching for "john's", the error I get is:
>
> Syntax error (missing operator) in query
> expression '(((ACCOUNT_LIST.ACCOUNT_NAME) LIKE '%
> john's%'))'.
>
> Any help would be great.  Thanks.
>
> Brian

Relevant Pages

  • Re: Search Form
    ... >Wrap the control reference with the Replace function to ... >character (that allows ACCESS to see it as a single, ... I have a form that builds an SQL statement ... But how can I make it accept the apostrophe? ...
    (microsoft.public.access.modulesdaovba)
  • Re: firefox handling of apostrophe in www address?
    ... The quote marks around the URI can be single or double, ... ASCII apostrophe and ASCII quotes. ... using %27 instead of ' in the HTML. ... ampersand character, it's used as a delimiter between parameters. ...
    (Fedora)
  • Re: Real Meaning of Apostrophe?
    ... You can use the format ... If the apostrophe is seen as formatting, why doesn't it get copied when I ... Special | Format to a cell that doesn't have one? ... Returns the prefix character for the cell. ...
    (microsoft.public.excel.programming)
  • Re: Apostrophe Creating a Merge Problem
    ... One other thing you could try is to use a different character in your data, ... > Excel file) in the particular Excel column that contains an apostrophe. ... > SQL ...
    (microsoft.public.word.mailmerge.fields)
  • Re: equal sign substituted for apostrophe
    ... apostrophe, instead of being the character from the default paragraph font, ... is a WP Typographic Symbols character. ... > One a couple of my workstations several word documents are converting an ... > apostrophe to an equal sign when the document is opened. ...
    (microsoft.public.word.application.errors)