Report

[Mihail]

Sorry. Forget point 1) from my last post. It work in report. My mistake. Again sorry !!!

[NeoPa]

Mihail:
This is not a very clever question, Vish, but I must ask: Is your control enough large to display entire value ?

@Vish
I think you may have missed this question from post #13, which is a shame as I believe it may be an important one. Try enlarging it greatly just to test the idea.

[Vish Narayan]

Mihail, thanks for the tip. I put this condition in the query and it worked finally!. Neopa, I did not want to enlarge the size since some percentages appeared too wierd to display. Thought better not to display at all unless it is within a range of +100% and -100%. Realized that "format" can be a very tricky function in a report. Mihail, I changed the condition of the "length" to include also the decimal point , as you indicated. I used the following condition in the query.=

IIf([z]=0,0,IIf([x]=0,0,IIf([z]<0 And [x]>0,0,IIf([z]>0 And [x]<0,0,IIf(Len(FormatPercent([z]/[x],2))>9,0,Round([x]/[z],2))))))

In the report, set the format to percent/2 decimals.Not sure if this is a sensible solution, but it worked. Thanks, guys!

[NeoPa]

First, please check out [CODE] Tags Must be Used.

Vish:
NeoPa, I did not want to enlarge the size since some percentages appeared too wierd to display.

I suggested you do a test Vish. Not change your design permanently. A test is to get the information as to what the problem is, without which you will certainly struggle to find an appropriate solution.

[Vish Narayan]

Neopa,When x and z were in decimals, the percentages displayed after enlargement of the control Y in the report design, were too big to even read like for example -698679.41%.

[NeoPa]

... and therein lies your problem it would seem. I hope you're now glad you did the test ;-)

How to resolve it is the next step. Such values indicate to me that either :

1. Your data is not sensible.
2. Your basic design is flawed as it cannot handle the data it needs to display.

Essentially, if X is so large in relation to Z then either that data is faulty, or else it's fine, in which case you haven't designed to allow for what you've got.

[Mihail]

As usually, NeoPa take my face :)

There are a lot of questions like WHY ?
1) Why you wish to handle that using Len() function ?
This can't be a good approache because:
Say you establish the number of digits to 3.
So you can show values between -99 to 999 (% of course). If you need to display 0.01 you can't ?!?!?
More, if you wish to display -0.01 you can't.
2) Why, if your logic is in numeric values ("the percentage can't be higher than... 500% and can't be less than... -400%") your option is to manage the number of digits to display and not the values itself:

IIF(([X/[Z] > 4) And ([X]/[Z]<5), [X]/[Z],"Out of range, my dear")

3) Why you need to calculate negative values for percentage ? This is not a "real" WHY. Maybe you have good reasons that I can't see.

[NeoPa]

Mihail:
As usually, NeoPa take my face :)

I'm not sure what that means exactly, but I'm sure it's good :-D

[Mihail]

"To take face" (to someone) means that you make the same things quicker, faster, better than the other one person.
I have hope to be an international expression but now I understand that it isn't :)