Format according to Iif statement

[adamlaing]

I have the following field value for a query:

UKper: IIf([Five Year Premium Analysis Geographic Export]![Geographic Total]>0,[Five Year Premium Analysis Geographic Export]!UK/[Five Year Premium Analysis Geographic Export]![Geographic Total],"^")

I want the calculated value to be displayed as a percentage when it appears and for the ^ to be stored as text. Any suggestions on how to do this? Currently instead of a percentage the values display as a long decimal and the ^ shows up as it should.

 

[Rich]

Look up the Format function

 

[Pat Hartman]

You will need to specifically format numeric value.

UKper: IIf([Five Year Premium Analysis Geographic Export]![Geographic Total]>0,Format([Five Year Premium Analysis Geographic Export]!UK/[Five Year Premium Analysis Geographic Export]![Geographic Total],"percent"),"^")

Since the IIf() returns a text value which cannot be formatted, Access won't format either value so you have to always return a text value. The embedded Format() function does that.

 

[adamlaing]

Pat, before I saw your post I tried the format function and could not get the syntax correct. What I ended up doing was using the FormatPercent function and it works the same:

UK_pecentage: IIf([Five Year Premium Analysis Geographic Export]!Geographic_total>0,FormatPercent(Nz([Five Year Premium Analysis Geographic Export]!UK,0)/Nz([Five Year Premium Analysis Geographic Export]!Geographic_total,0),0),"^")