Format % in query

[andy callaghan]

Hi , I have the query written and produces the correct results , however I would like to limit the results to two decimal places. It wont allow me to change this in field properties. The seond expression which is $ value does have the options in field properties to limit decimal places.

Margin%: IIf([Sum Of USD Value]<=0,"",([Sum Of USD Value]-[Sum Of WIF])/[Sum Of USD Value])

Margin: [Sum Of USD Value]-[Sum Of WIF ]

Any ideas anyone , as the report looks rubbish showing something 10 digits after the decimal !!

 

[EMP]

Margin%: IIf([Sum Of USD Value]<=0,"",([Sum Of USD Value]-[Sum Of WIF])/[Sum Of USD Value])

"" is a zero-length string so the whole column becomes text strings, not numbers.

You can use Null instead of "" in the IIF.

^

 

[andy callaghan]

thanks that works well and I can format as a percentage etc, I would now like to highlight the "null" value cells and show some text such as below FREE of Charge"

Margin%: IIf([Sum Of USD Value]<=0,"free of charge",NULL,([Sum Of USD Value]-[Sum Of WIF])/[Sum Of USD Value])

 

[Egg 'n' Bacon]

I think you'll end up with the old problem by mixing text results with number results; you'll loose the ability to set the number of decimal points.

 

[andy callaghan]

OK , Thanks EMP and Egg "n" Bacon its appreciated

Had a bit of a play and came up with this

Margin %: IIf([Sum Of USD Value]<=0,"free of charge",Round(((([Sum Of USD Value]-[Sum Of WIF])/[Sum Of USD Value])*100)))

Only it doesnt allow me to format with a % symbol , but I have the Column Header. Can you see any problem with this.??

thanks again

 

[andy callaghan]

Its good to have a sound board in this place just added more text at end of expression

&"%")

thanks again all

Results now look like this:-

Sum Of Qty Sum Of USD Value Sum Of WIF Margin %Margin

4 0.00 5,405.76 -5,405.76 free of charge
261 2,925.81 776.01 2,149.80 73%