Format as a percentage with an Iif

[adamlaing]

In one of my queries I calculate a percentage based on a change from one year to the next; however, sometimes there will not be any prior year data hence the percentage is undefined (i.e. current year amount/0=undefined). To stop the query from displaying an error when this is the case I wrote the following:

CapitalSurplusChange: IIf([P/Y Operating Data]![CapitalandSurplus]=0,"N/A",(Nz([C/Y Operating Data]![CapitalandSurplus],0)-Nz([P/Y Operating Data]![CapitalandSurplus],0))/Nz([P/Y Operating Data]![CapitalandSurplus],0))

I have the properties of the field set to percentage, but the calculation does not display as a percentage but as a long decimal. If I take out the Iif the percentages display fine however there are errors for the undefined calculations (hence why I inserted the Iif). How can I amend the code above to only display the "N/A" answers as text. I know about the formatpercent function, however I am exporting this output to excel and this when using the formatpercent or formatnumber operation the data still displays as text and not a number. Any ideas?

 

[adamlaing]

bump.........

 

[mresann]

The Iif function will always return BOTH calculations and display the calculation that satisfies the expression desired. Therefore, since "NA" is ALWAYS returned, even if not used, any number formatting on the field (including dates and percentages) revert to General.

Rather than formatting the FIELD attributes itself, format the INTERNAL CONDITIONS. I won't copy what you have exactly, rather look at the following:

DATAFIELD: Iif (Myexpression = 1, "NA", Format(Condition2, "Percent"))

You can transpose what you need and your query should come up correctly.

 

[Bat17]

You coud do with rethinking the IIF any way
in the first part you have
[P/Y Operating Data]![CapitalandSurplus]=0
but in the final part you have
Nz([P/Y Operating Data]![CapitalandSurplus],0)
If there is any chance that CapitalandSurplus could be null then you should be testing for it in the first part not the last!

Peter