Extended Price:

  • Thread starter Thread starter Guest
  • Start date Start date
G

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)
 
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
 
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.

Naresh Nichani said:
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



Bill said:
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)
Click to expand...
Click to expand...
 
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.
 
Back
Top