Report

[Vish Narayan]

I tried to run a report which shows the absolute values of X and a control called Y that displays values of X as a "percentage' with reference to the base values of another control say Z . The display format is set to percent and the decimals to display as 2. However when X and Z happen to be negative values, the control Y displays asterisks instead of 2 decimals percent. Tried to use various IIF conditions but still displays only the asterisks. How can I get the report to display the negative percentages to 2 decimals?

[NeoPa]

No obvious answer I can see, but then you don't include much info in the question. If you fix the question to include the basic information required you may find someone can answer it.

[Mihail]

What is the formula you use for Y control ?

[Vish Narayan]

The formula for Y control is : X/Z which is X value/ Z VALUE.

[Vish Narayan]

To make my formula more clear it is X value over Z Value. ( if x =75 and z=100 then y is 75% )

[Mihail]

:)
I think is no one here which don't know how to calculate a percentage.
The question is: What is the formula in the control's Control Source property?

[Vish Narayan]

More about this formula. If one of the values , x or z, is negative while the other is positive, the display turns into asterisks. I tried to put this condition in the control Y.

IIf([Z]=0,Null,IIf([Z]<0,IIf([X]>0,Null,IIf([Z]>0,IIf([X]<0,Null,[X/Z])

I think I cannot make this more clearer , but even when X and Z are positive both or negative both, the display is Null. Cannot make out where my conditions are conflicting.

[Mihail]

I think that the best thing is to use this formula:

=[x]/[z]

This will show an error if Z = 0 (Division by zero) that inform you that something is wrong

If you wish to manage the result as Null if Z=0 then:

=IIf([Z]=0,Null,[X]/[Z])

Also you can place an worning message if Z=0:

=IIf([z]=0,"Z = 0 ??? Bad for you !",[X]/[Z])

Paste any of this formulas in the control source of your text box
Entire formula, including the "=" sign.

[NeoPa]

What's the actual string value in the Format property of the control.

Mihail:
I think that the best thing is to use this formula:

=[x]/[z]

When Mihails says this I suspect he just means to try it to illustrate what you're working with - A temporary measure. It's not a good solution to your actual problem.

[Mihail]

Hi NeoPa !
Can you explain why you think that the first formula is not a good solution for this case ?
From my view point is the best because the percentage is, in fact, a division, and need that the low number (down to division line - I don't know the English word) must be not zero. I am very happy when Access worn me this way (without stop to work) when something is wrong.

Anyway I think that the main mistake (omission) of Vish is the omission of first "=" sign.
Then, trying and trying he complicate itself by using other and other formula for the control source.

[NeoPa]

Indeed Mihail.

For developers it may be ok for items to fail, but users should not be presented with error results. It's the responsibility of the developer to handle such situations smoothly. Relying on Access to capture such errors with default messages is pretty poor design.

Mihail:
Anyway I think that the main mistake (omission) of Vish is the omission of first "=" sign.

I'm not sure. If that were the case then why would it work for positive values?

I suspect their Format string is failing with negative results - hence the request to post it.

[Vish Narayan]

Mihail and Neopa, thanks for the input. I managed to resolve one part of the formula which is the previous formula not displaying the results even when values of both x and z were both positive or both negative. The percentage display of Control Y is ok now. The formula I put in Control Y :

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

My problem is still not completely resolved since I find that when one of the values of X or Z are too low like for example X is -0.150 and Z is 0.150 , the result of Y is 1000% . But the report displays asterisks and since I require the % display only if the result of Y is within say -100.00% and +100.00%. Else is should display Null. This is what I tried to put in my formula but this too does not work because if len of Y is more than 4 , Y still displays the asterisks. I wish it would be display Null if Len more than 4. The format display for Y is "percent"and "decimals" is 2.

[Mihail]

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

[Vish Narayan]

Further to the above, all that is required is if Y is not within the range of -100% and +100% , Y should display say, zero or Null,( can be anyone of these) since the percentages when displayed, would be weird. In my above formula I tried to manage this by putting in control Y : IIf(Len(Round([x]/[z],2))>5,0, but Y still displays asterisks when len is more than 5.

[Mihail]

I play a little bit with Round() function and I discover that:
1) Do not seems to work in the control source for a control (text box) in a report. I think that in Access's designers concept that is not necessary because when you establish the number of decimals for the control the control itself perform the Round() function;
2) Work very well in a query. So you must consider the option to design a query then to base your report to this query;

Now, at query level:
3) The Round() function cut the decimals in excess (if exist) but not add decimals to the result;
4) The Len() function has as result a string (this is not a "discover") so, if the decimal point appear (exist) then it is counted to the length of the string, if not... not. So is not impossible to manage your problem via Len() function but is very hard. You can simple manage that by compare with a numeric value:

IIF(Round(X/Z)>100 , ValueIfTrue , ValueIfFalse)

I have attached a small pic to illustrate points 3) and 4)

[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 :)