Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

problematic use of apostrophy in SQL statement

Discussion in 'Information Technology' started by Philip Leduc, Jul 28, 2006.

  1. Philip Leduc

    Philip Leduc
    Expand Collapse
    Guest

    I need to solve the following problem:
    To do filters on a recordset I use a SQL statement, the advantage is that I
    can then use that statement to change the recordsource of a subform that
    uses the same query as that recordset, a report that uses the same recordset
    or another form that uses the same recordset.
    strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
    Me.txtChooseNameLike & "*'"

    (I also use this statement in a more complex form with a where clause that
    can include multiple checks that results in a composed statement such as
    strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
    Me.txtChooseNameLike & "*' AND [custype] = " & me.cboChoosetype

    This works great until the company name (Me.txtChooseNameLike) contains an
    apostrophy! Does anyone see a solution?
     
  2. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Rather than using single quotes (apostrophe) character use a double set of
    double quotes characters when building the string expression. A double set
    of quotes within a delimited string is interpreted as a single literal quotes
    character:

    "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like ""*" &
    Me.txtChooseName Like & "*"" AND [custype] = " & me.cboChoosetype

    You can also use the Chr(34) function call, 34 being the ANSI code for the
    double quotes character:

    "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like " & Chr(34) &
    "*" & Me.txtChooseName Like & "*" & Chr(34) & " AND [custype] = " &
    me.cboChoosetype

    This assumes there are no embedded double quotes characters in the company
    name of course! Its unlikely, but should that be the case you'd need to
    write your own function which returns a string in which the quotes characters
    in the value passed into the function are replaced with some other character
    (the tilde is commonly used) and apply the function to the values both of the
    txtChooseName text box and the cuscompanyName column. You then compare the
    return values of the function rather than the values of the text box and
    column.

    Ken Sheridan
    Stafford, England

    "Philip Leduc" wrote:

    > I need to solve the following problem:
    > To do filters on a recordset I use a SQL statement, the advantage is that I
    > can then use that statement to change the recordsource of a subform that
    > uses the same query as that recordset, a report that uses the same recordset
    > or another form that uses the same recordset.
    > strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
    > Me.txtChooseNameLike & "*'"
    >
    > (I also use this statement in a more complex form with a where clause that
    > can include multiple checks that results in a composed statement such as
    > strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
    > Me.txtChooseNameLike & "*' AND [custype] = " & me.cboChoosetype
    >
    > This works great until the company name (Me.txtChooseNameLike) contains an
    > apostrophy! Does anyone see a solution?
    >
    >
    >
     
  3. Delordson Kallon

    Delordson Kallon
    Expand Collapse
    Guest

    Hi Philip,

    You have come across the famous apostrophy problem. One solution is to use
    the replace function to replace all apostropies with double apostrophies.
    Something like this...

    strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
    Replace(Me.txtChooseNameLike , "'", "''") & "*' AND [custype] = " &
    me.cboChoosetype

    i.e. replace single apostrophy (') with with a pair of apostrophies ('')
    both enclosed in double quotation marks. Look up help on the replace function
    for more info.

    HTH
    Delordson Kallon
    www.instantsoftwaretraining.com


    "Philip Leduc" wrote:

    > I need to solve the following problem:
    > To do filters on a recordset I use a SQL statement, the advantage is that I
    > can then use that statement to change the recordsource of a subform that
    > uses the same query as that recordset, a report that uses the same recordset
    > or another form that uses the same recordset.
    > strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
    > Me.txtChooseNameLike & "*'"
    >
    > (I also use this statement in a more complex form with a where clause that
    > can include multiple checks that results in a composed statement such as
    > strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
    > Me.txtChooseNameLike & "*' AND [custype] = " & me.cboChoosetype
    >
    > This works great until the company name (Me.txtChooseNameLike) contains an
    > apostrophy! Does anyone see a solution?
    >
    >
    >
     
  4. John W. Vinson/MVP

    John W. Vinson/MVP
    Expand Collapse
    Guest

    "Philip Leduc" <ph.leducREMOVE@comcast.net> wrote in message
    news:%23OeJQLOhGHA.1612@TK2MSFTNGP04.phx.gbl...
    >I need to solve the following problem:
    > To do filters on a recordset I use a SQL statement, the advantage is that
    > I can then use that statement to change the recordsource of a subform that
    > uses the same query as that recordset, a report that uses the same
    > recordset or another form that uses the same recordset.
    > strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*"
    > & Me.txtChooseNameLike & "*'"
    >
    > (I also use this statement in a more complex form with a where clause that
    > can include multiple checks that results in a composed statement such as
    > strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*"
    > & Me.txtChooseNameLike & "*' AND [custype] = " & me.cboChoosetype
    >
    > This works great until the company name (Me.txtChooseNameLike) contains an
    > apostrophy! Does anyone see a solution?


    If you can safely assume that txtChooseNameLike will never contain a
    doublequote character " then you can delimit the criteria string with "
    rather than with '; to do so, use two consecutive doublequotes within the
    doublequote delimited string constant:

    strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like ""*"
    &
    Me.txtChooseNameLike & "*"""

    This will evaluate to

    like "O'Niell"

    which will search correctly.

    If you might have criteria containing doublequotes (e.g. a company named
    Joe's "Eclectic" Establishment), you can replace the ' with two consecutive
    ':

    strSQL = "SELECT * FROM QrycustomersReport WHERE [cuscompanyName] like '*" &
    Replace(Me.txtChooseNameLike, "'", "''") & "*'"

    For readability, that is one apostrophe between quotemarks as the second
    argument of Replace, and two consecutive apostrophes between quotemarks as
    the third.

    --
    John W. Vinson/MVP
     

Share This Page