Format Numbers in IIF statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

alecgreen - 30 Jun 2006 19:03 GMT

Hi, I am using the following IIF statement to calculate the percentage
difference between a Selling Price and a Order Selling Price, bearing
in mind you cannot deivide by zero!

OrdvsSell: IIf([SellingPrice]=0,"NO
CALC",(([OrderSellingPrice]-[SellingPrice])/[SellingPrice]*100))

However I cannot format the result to say 2 decimal places, instead of
the actual calculated result - Can this be done within the IIF
statement?

Thanks

Alec

Reply to this Message

Marshall Barton - 30 Jun 2006 19:29 GMT

>Hi, I am using the following IIF statement to calculate the percentage
>difference between a Selling Price and a Order Selling Price, bearing
[quoted text clipped - 6 lines]
>the actual calculated result - Can this be done within the IIF
>statement?

The problem probably is that the result is being converted
to a string because of the possible "NO CALC" result.

Try this:

OrdvsSell: IIf([SellingPrice]=0,"NO
CALC",Format(([OrderSellingPrice]-[SellingPrice])/[SellingPrice]),
"0.00%" )

Signature

Marsh
MVP [MS Access]

Reply to this Message

Jeff L - 30 Jun 2006 19:40 GMT

Yes.
OrdvsSell: IIf([SellingPrice]=0,"NO
CALC",Round((([OrderSellingPrice]-[SellingPrice])/[SellingPrice]*100),2))
This will round your result to 2 decimal places.

Reply to this Message