Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL in code (A97)

    Hi, am new to VBA and am trying to alter the following line of code where DiscountPrices.Code is not 'w02' OR 's03', but am getting tied up in my brackets!! Can anyone help???

    dbs.Execute "UPDATE tblDiscountPrices SET tblDiscountPrices.PriceSC = ([Price]-20)+[Tax] WHERE (((tblDiscountPrices.DiscountDepart)='MON') AND ((tblDiscountPrices.Price)>=199) AND (Not (tblDiscountPrices.Code)='W02'));"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL in code (A97)

    Access always generates a lot of superfluous parentheses and references to the table name in its queries. This is necessary because you might modify the design of the query later on. When you write SQL strings in code however, there is no need for this. To check if something is A or B or C, you can use <font face="Georgia">In('A', 'B', 'C')</font face=georgia>. To check if something is not A, B or C, use <font face="Georgia">Not In('A', 'B', 'C')</font face=georgia>. So in your example:

    <font face="Georgia">dbs.Execute "UPDATE tblDiscountPrices SET PriceSC = ([Price]-20)+[Tax] WHERE DiscountDepart='MON' AND Price>=199 AND Code Not In('W02', 'S03')"</font face=georgia>

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in code (A97)

    Many Thanks Hans, will have a shot at this.

    Marion

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

    Re: SQL in code (A97)

    You can post your Access VBA questions in the Access forum, Marion. There isn't any particular need to post them here.
    Charlotte

Posting Permissions

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