Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: & in field problem

    I have a problem with the & in the value of a string. It is basically messing up my select statement. For example, I have a business name in the BusinessName field called: B & D's Village Inn. I can get pass the ' by using the code below, but I can't get by the &. Any suggestions would be greatly appreciated.

    I've tried:
    ....
    & " WHERE (((dbo_WECCCustomersAddresses.PKCustomerID)= " & Chr(34) & PKID & Chr(34) & ""

    and also:
    ....
    & " WHERE (((dbo_WECCCustomersAddresses.PKCustomerID)= """ & PKID & """"

    where PKID is a variant which loops through and holds the business Name and PKCustomerID is the table field containing the B & D's Village Inn value. I have several business name entries similar to this so doing something on a case by case basis is not feasible.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What error do you get?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    In order to use the ampersand in a string it has to be doubled because the ampersand IS the token for escape sequences ... So, to have the string display properly ... Bob && Carol's Pizza Parlor ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by pkstormy
    I've tried:
    ....
    & " WHERE (((dbo_WECCCustomersAddresses.PKCustomerID)= " & Chr(34) & PKID & Chr(34) & ""

    and also:
    ....
    & " WHERE (((dbo_WECCCustomersAddresses.PKCustomerID)= """ & PKID & """"
    Shouldn't it be...
    & " WHERE (((dbo_WECCCustomersAddresses.PKCustomerID)= " & Chr(34) & PKID & Chr(34)

    I've had the following working:
    Code:
    myCond = "D & V's"
    
    mySQL = "UPDATE Table1 SET description = 'xxx' WHERE reference =" & Chr(34) & myCond & Chr(34)
    DoCmd.RunSQL mySQL
    I might be fundamentally missing the point though
    hth
    Chris

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    & in syntax

    Thanks howey,

    I'll give that a try.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    & in syntax

    It worked! I had a few extra ((( and also removed the "" at the end. Guess I had a brain lapse on the syntax. Thanks for the help all.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pkstormy
    It worked! I had a few extra ((( and also removed the "" at the end. Guess I had a brain lapse on the syntax. Thanks for the help all.
    Although the query builder is helpful for throwing some SQL together that is precisely why my first job is usually to remove all those god awful brackets Access throws into every bit of white space it can get its hands on!
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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