Reporting Sevices iif condition 1

[Waynest]

Hello mates

Any ideas why this would produce #Error instead of 0 on my report? LPrice is zero on the rows displaying the error.


=iif((Fields!LPrice.Value <> 0) And (Fields!SPrice.Value <> 0) and (Fields!LPrice.Value < Fields!SPrice.Value) ,((Fields!SPrice.Value - Fields!LPrice.Value) / Fields!S
Price.Value),0)

 

[Waynest]

BTW its SQL 2005, should have said.

 

[SgtJarrow]

You have three conditions and even if the SPrice = 0 the whole will result in ERROR because the whole statement is evaluated before an answer is returned. Since you seem to be trying to prevent division by zero error, here is something I found that works well.... You should be able to adapt it for your needs.

=FormatPercent(IIF(Sum(Fields!PYCE.Value) <> 0, (Sum(Fields!CYCE.Value) - Sum(Fields!PYCE.Value)) / IIF(Sum(Fields!PYCE.Value) <> 0, Sum(Fields!PYCE.Value), 1), 0),2)

You'll notice the second IIF embedded in the denominator of the equation...This sets the denominator to 1 if it actually 0.

This works because the inner IIF is evaluated before the outer and therefore the outer results in a division by 0 error.

Hope it helps.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SgtJarrow]

That last line should be:

therefore the outer IIF never results in a division by 0 error.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Waynest]

aah, I suspected it was still dividing by zero despite the IIF.

Thanks, thats fixed it

 

[xlbo]

BODMAS !!!!!

Brackets then
Orders (Powers etc) then
Division then
Multiplication then
Addition then
Subtraction



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question