price calculation

[josros60]

hi,

I have products that have price per box and per unit,

for example box price is $64 in the box has 9 units so the price per unit is $7.11.

the problem is the in the report i have it times it by box price but some products are deliver per unit so the price should be calculated by unitprice not price box in the report.

my question is:

how can i do a iif condition formula to to check if is price box or unit price?

I have this but it's not working:

=IIf([UNIT]="EACH",Sum([UnitCost]*[Quantity]),Sum([UnitPrice]*[Quantity]))

thank you

 

[Phildaburn]

Remove the SUM() code like this:

=IIf([UNIT]="EACH",[UnitCost]*[Quantity],[UnitPrice]*[Quantity])

This will give you what you're looking for.

 

[josros60]

Thank you.

When printed the report works but only on the product by each and the other ones shows error so only calculate the each ones.

thanks

 

[alansidman]

Are you basing your report off a query? If so, post your SQL statement for your query. If you are basing your report directly off a table, consider changing it to running a query first.

Alan

 

[josros60]

Thank you.

here my sql query, yes i am basing the report on this query and i put the calculation on a textbox.

SELECT tblPurchaseOrders.PONumber, tblPurchaseOrders.PODate, tblPurchaseOrderDetails.fkPurchaseOrderID, tblPurchaseOrderDetails.fkProductID, tblPurchaseOrders.pkInvoiceNumber, tblPurchaseOrderDetails.Quantity, tblProducts.UNIT, tblProductCategories.Category, tblProducts.vpCode, tblProducts.UnitPrice, tblProducts.ProductName, tblProductCategories.PrintFlag, +[UnitPrice]/[QuantityPerUnit] AS UnitCost
FROM tblPurchaseOrders INNER JOIN (tblProductCategories INNER JOIN (tblProducts INNER JOIN tblPurchaseOrderDetails ON tblProducts.pkProductID = tblPurchaseOrderDetails.fkProductID) ON tblProductCategories.pkProductCategoryID = tblProducts.pkProductCategoryID) ON tblPurchaseOrders.pkPurchaseOrderID = tblPurchaseOrderDetails.fkPurchaseOrderID
WHERE (((tblPurchaseOrders.PODate)>=[forms]![ReportDateRange4]![BeginDate] And (tblPurchaseOrders.PODate)<=[forms]![ReportDateRange4]![EndDate]));

thanks