Re: Update SET ?

From: Ray Costanzo [MVP] (my)
Date: 08/30/04

  • Next message: Jeff Dillon: "Re: JET error in interdev debug only (DCOM error?)" 
    Date: Mon, 30 Aug 2004 15:24:59 -0400

    As for the "

    This isn't an issue with building your query. It is merely an issue with
    building a string. Example:

    sVar = "The sign over the door says "Employees Only.""

    When this is interpreted by the scripting host, it will see that " before
    Employees and take that to mean the end of your string. Then, any
    characters that appear after it will just cause confusion, hence an error.
    The way to "escape" quotes in VB Script (is that what you're using) is by
    doubling them. IE:

    sVar = "The sign over the door says ""Employees Only."""

    --------------------------------------

    As for the '

    ' is the delimiter used mostly for text in SQL queries. If you have a ' in
    your text that you're updating, inserting, or whatever, you need to "escape"
    that for the query. This is done by doubling the ' character. So, if you
    have this:

    sLastname = "O'Brien"

    You'd want to essentially do:

    sSQL = "select something from somewhere where lastname='" &
    Replace(sLastname, "'", "''") & "'"

    That would produce a sSQL value of

    select something from somewhere where lastname='O''Brien'

    ---------------------------------------

    For more detailed assistance, post your code and your errors when you are
    posting about code and errors.

    Ray at work

    "TomT" <tomt@adslweb.co.uk> wrote in message
    news:41337b84$0$20248$cc9e4d1f@news-text.dial.pipex.com...
    > Taking data from a form, and using UPDATE tablename SET
    >
    > Seems to be working fine... EXCEPT..
    >
    > Certain characters cause an error when the update happens.
    >
    > These include:
    > ' "
    >
    > ' causes an SQL error (Syntax error (missing operator) in query
    > expression)
    >
    > " data after or between the "" is not displayed..
    >
    > How do I trap these ? and stop these errors ?
    >
    > Thanks
    >

  • Next message: Jeff Dillon: "Re: JET error in interdev debug only (DCOM error?)"

    Relevant Pages

    • Re: Self-Documenting Code Contest
      ... self-documenting. ... query:= 'documenting' asSortedCollection. ... string size < query size ... two words becomes a set of Characters. ...
      (comp.lang.smalltalk)
    • Re: formatting numbers after using the nz function
      ... Especially since Access can treat a string of number characters as a number in many cases. ... "John Spencer" wrote: ... I can format the resulting numbers in the query and in a report as standsrd with two decimal places. ...
      (microsoft.public.access.queries)
    • Re: Query returns no data
      ... I have constructed my query to direct to this database with the search field "subject" in ascending order. ... Compares a string expression to a pattern in an SQL expression. ... Multiple characters a*a aa, aBa, aBBBa aBC ...
      (microsoft.public.access.gettingstarted)
    • Re: VBA Recordset Query String Length Limit
      ... When the string that is used as the SQL query is greater than 256 characters ...
      (microsoft.public.access.modulesdaovba)
    • Re: Problems with Delete Command
      ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
      (microsoft.public.access.tablesdbdesign)
    • Quantcast