#Error: when dividing by 0

[bluemoon9]

Hi all,
I have this sql in my querry to calcuate the rate:
Rate:([Yes]/([Yes]+[No])). And in my report, I set the format of the field to "percentage", but then when the sum of Yes + No is =0, it shows #Error, and I do not want the report to have "#Error", instead I would like it to say: "Dem 0", then I modify my sql as follow:
Rate: IIf(([Yes]+[No])=0,"Dem 0",([Yes]/([Yes]+[No])))
the "Dem 0" works well for the ones with demoniator=0, but then I can't set the format on the form to "percentage" anymore, so it'll say 0.333333, instead of %, for the ones with demoniator is not euqal to 0.

Can someone please help? basically, I would like to get rid of the "#Error", when the demoninator =0, but still be able to keep the % format.

Thank you!
bluemoon

[bluemoon9]

I tried this way and it works well, but then I run into the problem of, for example # such as 0.333333333, when *100, it becomes 33.33333333, how can I cut of at 33.33 only?
Rate: IIf(([Yes]+[No])=0,"Dem 0",([Yes]/([Yes]+[No])*100 &"%"))

thank you!

bluemoon

[ChipR]

Try
IIf ( [Yes]+[No])=0,"Dem 0", Format( ([Yes]/[Yes]+[No]), "Percent") )

[bluemoon9]

Hi, it doesn't work, because of "percent" in quotation, I've tried to put (), but did not work either.
thanks!

bluemoon

[ChipR]

You may be missing a parenthesis or comma. What is the error you're getting specifically?

[bluemoon9]

Hi,
I've tried to put Format$, instead of Format only and it works.
Thanks

bluemoon

[NeoPa]

@ChipR
This would be clearer in [ CODE ] tags, but the problem here is that there are parentheses () around the [Yes]+[No] immediately after IIf.

This should be a workable solution, to avoid using out-of-date function calls which won't be supported forever :

Format(IIf([Yes]+[No]=0,'Dem 0',[Yes]/([Yes]+[No])),'Percent')

[NeoPa]

NB. It's very important to remember that with this solution (any of those mentioned in fact) the result is now a string and not a numeric entity. Generally to be avoided in SQL where possible.

Without knowing the context it's hard to say where, but it's better practice to format the number only at the point of display. Specifically not at the point of calculation.

[ChipR]

You're right, I missed that parenthesis in the first part of the IIF. Sorry about that.
However, I don't see how CODE tags could possibly make it any clearer, unless you prefer a smaller font, nor why you moved the IIf inside the Format function.

[NeoPa]

Proportional fonts are not good for determining easily, exactly what is displayed. That's the main reason why they're never used for displaying code. This web font in particular does not differentiate clearly between many characters and is known to lead to confusion. Hence the site rule that all code (with the possible exception of 1 liners) must be posted within the CODE tags.

The IIf() inside the Format() is simply a matter of tidiness. I was keeping the code that determined the value together, then applying formatting to the calculated result. Experience has taught me that far fewer errors occur when thinking in logical progression.

If that sounds like criticism of your code, that is not my intention. You asked the question, I answered it as I see it.

[ChipR]

No problem, I respect your opinion. But applying the Format to the entire result is not a logical progression the way I see it, since the result of the IIf may be a String, and not need formatting at all. You only need to Format the part that is a number, and I found myself wondering what happens if you try to Format 'percent' on a string like 'Dem 0'. It's fine for those who know that the Format function won't have any affect on the string, but who wants to assume when it's unneccesary?