Query IIF expression that displays either text or a number in percent format

[Eddy Sincere]

I have an IIF expression that returns one of two values: 1) text or, 2) a calculation that I want displayed in a percent format. My problem is that I can't get the calculation to display as a percent.



Here's the IIF expression:

E3: IIf([Qntty]/[VMDV]<1,"<1%",[Qntty]/[VMDV])

[Qntty] = Quantity; Example: 10

[VMDV] = Quantity; Example: 1.5

Actual IIF Result = 6.6666...7

Desired IIF Result = 667%


When I add "Percent" into Properties, Format it doesn't change anything. Probably because the IIF can display a text or numerical value. Should I add something in the Criteria?

I can post the query or DB if you'd like. Thanks in advance for any advice.

[ssanfu]

Maybe

E3: IIf([Qntty]/[VMDV]<1,"<1%",Format([Qntty]/[VMDV],"##.#%))

or

E3: IIf([Qntty]/[VMDV]<1,"<1%",Format([Qntty]/[VMDV],"#%))

The result of the Format() function will be a string.

[CJ_London]

you will have problems trying to have iif return either a text or a number - the vba interpreter will treat both as text - and you cannot format text using a control format property. However as steve suggests, you can use the format function to return a text value - you just won't be able to do any math manipulation on the result.

[ssanfu]

Good point, Ajax. Much clearer than what I posted.

[Eddy Sincere]

Excellent solution. And, thanks to Ajax (Francis?) for clarifying.

I had to make one adjustment to the expression by adding quotation marks to close the argument. Now it looks like:

E3: IIf([Qntty]/[VMDV]<1,"<1%",Format([Qntty]/[VMDV],"#%"))

(obscure Deadpool reference. )