Extended Price:

[Guest]

Currently, the query expression looks like this - ExtendedPrice:CCur(([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*1)*107.6 This is
calculating extended price with sales tax. I need to specify a particular
[productID] that is "non-taxable".

So the expression would say something like - ExtendedPrice: (IF
[PRODUCTID]<>"968") CCur(([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*1)*107.6 (OTHERWISE
UnitePrice)

 

[Naresh Nichani]

Use the IIF function.

Like this
IIF(Condition,TrueValue,False)

So it could be like this

IIF([PRODUCTID]<>"968"), CCur(([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*1)*107.6,CCur(([Order
Details].UnitPrice)

Regards,

Naresh Nichani
Microsoft Access MVP

 

[John Spencer]

Of course, this works.
HOWEVER, are you going to have more products that are non-taxable? If so, a
better solution is to add a non-taxable column to your products table, so
you can test it to decide when to "add" the tax.

Use the IIF function.

Like this
IIF(Condition,TrueValue,False)

So it could be like this

IIF([PRODUCTID]<>"968"), CCur(([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*1)*107.6,CCur(([Order
Details].UnitPrice)

Regards,

Naresh Nichani
Microsoft Access MVP

Currently, the query expression looks like this -
ExtendedPrice:CCur(([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*1)*107.6 This is
calculating extended price with sales tax. I need to specify a particular
[productID] that is "non-taxable".

So the expression would say something like - ExtendedPrice: (IF
[PRODUCTID]<>"968") CCur(([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*1)*107.6 (OTHERWISE
UnitePrice)

 

[Guest]

I kept fighting with it into the wee hours and got it working. It's been
waaayyy to long since I used this to the extent I am now.

This is what worked.
ExtendedPrice: CCur(IIf([Order Details].productID=975,[Order
Details].UnitPrice,[Order
Details].UnitPrice*[Quantity]*(1-[discount]/1)*107.6/100))

I don't completely understand why but it works. If I move the division by
100 to the division next to "discount" as it was originally, it doesn't work.

Most sales will not include "non-taxible" but by adding a product code 975
for non-taxible, I can chose that at any point during input of items
purchased.