Calculate margin in query - result not quite right

[KevinSlater]

Hi,
I have the following code in a query:

NEWMARP: IIf([DATA-PRICE_BOOK_ITEMS]![PRICE_NEW]<>0,(([DATA-PRICE_BOOK_ITEMS]![PRICE_NEW]/[CURRENCY_RATE])-[EXT-STK_PARTS]![STK_COST_CURR]/100000)/[DATA-PRICE_BOOK_ITEMS]![PRICE_NEW]/[CURRENCY_RATE])*100,0)

If the Field "STK_COST_CURR" = 0.48
if the Field "PRICE_NEW = 3.57

then 0.48 /3.57 should = 0.1344 but instead when i run the query the number is 13.34. im gueesing maybe i have a bracket in the wrong place? but not sure how to correct this?

The calculation result is not quite right, any suggestions would be great.

 

[KenHigg]

Here's a stab in the dark - The *100 is pushing the decimal to the right 2 places.

 

[DCrake]

it would be far easier to manage if you created a custom function and placed it in a module and subsequently refer to it in your query

NEWMARP: IIf([DATA-PRICE_BOOK_ITEMS]![PRICE_NEW]<>0,

(([DATA-PRICE_BOOK_ITEMS]![PRICE_NEW]/[CURRENCY_RATE])-[EXT- STK_PARTS]![STK_COST_CURR]/100000)/[DATA-PRICE_BOOK_ITEMS]![PRICE_NEW]/[CURRENCY_RATE])*100,

0)

Would become

Public Function calcMargin(NewPrice As Double,CurrencyRate As Double,CostPrice As Double) As Double

If NewPrice <> 0 Then
   calcMargin = ((NewPrice/CurrencyRate)-(CostPrice/100000))/(NewPrice/CurrencyRate)*100
Else
    calcMargin = 0
End If

End Function

Don't know if I have read your formula correctly but thats what it loooks like.


Then in your query

NEWMARP:calcMargin( [PRICE_NEW],[CURRENCY_RATE],[STK_COST_CURR])

David

 

[namliam]

NEWMARP: 
IIf([DATA-PRICE_BOOK_ITEMS]![PRICE_NEW]<>0,
    (
     ([DATA-PRICE_BOOK_ITEMS]![PRICE_NEW]/[CURRENCY_RATE]
     )-
     [EXT-STK_PARTS]![STK_COST_CURR]/100000
    )/[DATA-PRICE_BOOK_ITEMS]![PRICE_NEW]/[CURRENCY_RATE]
   )*100,0
)

Did anyone notice or did I make a mistake?? There is a ) to many?

Also to actaully calculate the formula we need the currency rate too....

 

[KevinSlater]

Ken: your solution did the trick thanks.
David: thanks for your comments, thats seems a better/cleaner way of manging the code.
namliam: yes good point, ideally need the currecy rate, but for now the calculation gives the answer needed in this case.

Thanks all for your replies.

 

[KenHigg]

Glad you have it working -