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?
-
-
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. -
What is the formula you use for Y control ?Comment
-
The formula for Y control is : X/Z which is X value/ Z VALUE.Comment
-
To make my formula more clear it is X value over Z Value. ( if x =75 and z=100 then y is 75% )Comment
-
:)
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?Comment
-
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.
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.Code:IIf([Z]=0,Null,IIf([Z]<0,IIf([X]>0,Null,IIf([Z]>0,IIf([X]<0,Null,[X/Z])
Comment
-
I think that the best thing is to use this formula:
This will show an error if Z = 0 (Division by zero) that inform you that something is wrongCode:=[x]/[z]
If you wish to manage the result as Null if Z=0 then:
Also you can place an worning message if Z=0:Code:=IIf([Z]=0,Null,[X]/[Z])
Paste any of this formulas in the control source of your text boxCode:=IIf([z]=0,"Z = 0 ??? Bad for you !",[X]/[Z])
Entire formula, including the "=" sign.Comment
-
What's the actual string value in the Format property of the control.
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.Originally posted by MihailComment
-
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.Comment
-
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.
I'm not sure. If that were the case then why would it work for positive values?Originally posted by Mihail
I suspect their Format string is failing with negative results - hence the request to post it.Last edited by NeoPa; Mar 9 '12, 11:45 PM.Comment
-
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 :
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"an d "decimals" is 2.Code: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))))))
Comment
-
This is not a very clever question, Vish, but I must ask: Is your control enough large to display entire value ?Comment
-
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.Comment
-
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:
I have attached a small pic to illustrate points 3) and 4)Code:IIF(Round(X/Z)>100 , ValueIfTrue , ValueIfFalse)
[imgnothumb]http://bytes.com/attachments/attachment/6205d1331361154/report_round.jp g[/imgnothumb]Comment
Comment