SQL in code (A97)

Topic

New Reply

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′));”

Reply | Quote

Replies

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 In(‘A’, ‘B’, ‘C’). To check if something is not A, B or C, use Not In(‘A’, ‘B’, ‘C’). So in your example:

dbs.Execute “UPDATE tblDiscountPrices SET PriceSC = ([Price]-20)+[Tax] WHERE DiscountDepart=’MON’ AND Price>=199 AND Code Not In(‘W02’, ‘S03’)”

Reply | Quote

Many Thanks Hans, will have a shot at this.

Marion

Reply | Quote

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

Reply | Quote