Percent format in IIF statement

[Guest]

Hi,
I am facing the same problem.
I have two calculated fields in my query, one is:---
Percent Non-Discounted: IIf([No of orders]>0,Format([No of NonDiscounted
orders]/[No of Orders],"Percent"),"N/A")
This one works fine. But I have another field which calculates percentage of
disounted orders. I have set it up as :
Percent Discounted: IIf([No of orders]>0,Format(1-[Percent
Non-Discounted],"Percent"),"N/A")
but this is not working?

 

[Guest]

neeraj,

For the [Percent Discounted] use

iif ([No of orders]>0,Format([No of orders]-[No of NonDiscounted
orders]/[No of Orders],"Percent"),"N/A")

Format() returns a string, not a number, so 1 - [Percent Non-Discounted]
doesn't work (can't do arithmetic with a string). Since the '%' is part of
the string, you can't convert back to a numeric either (well, val() would do
it, but then you still have to divide by 100 for the percent format to work
and your expression looks horrendous).

Good Luck!