Results 1 to 12 of 12

Thread: SQL (Access 2k)

  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL (Access 2k)

    I hope you can suggest some alternatives to a problem I have.

    I have created a form where the user selects two vendor names that I will use in an SQL statement . cboStart and cboEnd are the results of a dropdown combo list. The code is working fine until either the start name or the end name has an apostrophe in the name. Then the VB has real problems with setting up the code. Can you change the delimiter used by VB in a string?

    This is what the code actually looks like:

    ws_Sql = "UPDATE tbl_Vendor SET Active = True"
    ws_Sql = ws_Sql & " WHERE ((Vendor_Name) Between "
    ws_Sql = ws_Sql & "'" & cboStart & "' And '" & cboEnd & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL ws_Sql
    DoCmd.SetWarnings True


    I have an example where the cboStart = Anderson's Flowers. The apostrophe is the issue. If I could use another character in the SQL then Anderson's would not fail.

    Thanks,
    Tom

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL (Access 2k)

    Try double "Double Quotes".
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: SQL (Access 2k)

    Instead of trying to embed an apostrophe like this "'", which is hard to read and produces the problem you described, I use chr(34) to embed a quote, so instead of:

    ws_Sql = ws_Sql & "'" & cboStart & "' And '" & cboEnd & "');"

    You will do this:

    ws_Sql = ws_Sql & chr(34) & cboStart & chr(34) " And " & chr(34) & cboEnd & chr(34) & ");"

    BTW, you might reconsider using VendorNames as a Primary Key, as they can easily duplicate. It is much better in the long run to use a VendorID (such as an autonumber) behind the scenes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL (Access 2k)

    Thanks for the suggestion Mark but changing apostrophe for chr(34) only makes the code easier to read. The problem is when the docmd executes the code the single quote in the data interferes with the single quotes imbedded in my SQL. Is there some other way to imbed my SQL and not worry about the content of the selected names?
    Thanks for the suggestion on the key. In fact the primary key is vendor number. The client just likes to work with names instead of numbers.

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

    Re: SQL (Access 2k)

    I think you misunderstood. The Chr(34) is the ASCII double quote character, not the apostrophe or single quote. What Mark was suggesting was that you not try to use single quotes around the string being evaluated. Instead, using the Chr(34) gives you a string wrapped in *double quotes*, and those aren't affected by the apostrophe inside the string.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL (Access 2k)

    Charlotte,

    I did misunderstand. Thanks for clearing it up for me. Did try the change to my code before I posted my reponse to Mark and it did not clear up the problem. But now I know why. Unfortunately some of my data uses double quotes in the name. When I tested Mark's suggestion I happened to use the double quote data. So now my problem is can I use something other than single quote or double quote to delimit my text fields?

    Tom

  7. #7
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL (Access 2k)

    I have resolved it. I will test each field to see if they contain a single or a double quote and use the other type of quote as a delimiter. Thanks again for you help.

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL (Access 2k)

    Now to throw a monkey wrench into the mix <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    What if a Field contains both? That solution will not work.

    I think, and I haven't tested it, that you will need to run the field values through a function, before putting them in the SQL statement, to escape the quotes so they don't cause problems.

    I think that they need to be doubled up in the SQL statement, but I'm not sure.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  9. #9
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL (Access 2k)

    Thanks for the monkey wrench. If it wasn't for those pesky users this programming thing would be easy. I am not sure what you mean by escaping the quotes.

    Tom

  10. #10
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL (Access 2k)

    Glad to help <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    By escaping the quotes, I mean putting two double quotes in the string if the string is delimited by double quotes.

    5" high would become "5"" high"

    OR put two single quotes if the value is delimited by single quotes

    John's Place would become 'John''s Place'

    Make more sense?
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

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

    Re: SQL (Access 2k)

    Take a look at this post from HansV and see if that helps.
    Charlotte

  12. #12
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL (Access 2k)

    Thanks. I will incorporate that into my database. Thanks again for all your help.

    Tom

Posting Permissions

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