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?
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?