Calculate Percentage - Division By Zero

[LB79]

Hello,

Im trying to work out a percentage within a query, but when I do the calculation I get the error "Division By Zero". Can anyone advise me on this? SQL below.

Thanks

SELECT PMA_tblPMA_Export.RepMnth, PMA_tblPMA_Export.[UN Code], Sum([PMAOfSum]/[CUROfSum]) AS [Ratio (NET)], PMA_tblPMA_Export.PMAOfSum AS [PMA (NET)]
FROM PMA_tblPMA_Export
GROUP BY PMA_tblPMA_Export.RepMnth, PMA_tblPMA_Export.[UN Code], PMA_tblPMA_Export.PMAOfSum
HAVING (((PMA_tblPMA_Export.RepMnth)="200909"))
ORDER BY PMA_tblPMA_Export.RepMnth, PMA_tblPMA_Export.[UN Code];

 

[boblarson]

Change this:

Sum([PMAOfSum]/[CUROfSum]) AS [Ratio (NET)],

to this:

Sum(IIf([CUROfSum]<>0,[PMAOfSum]/[CUROfSum],1)) AS [Ratio (NET)],

I put a 1 if the CUROfSum is 0 but you can make it whatever you want - 0 or whatever.

 

[MarkK]

That might still fail. See how it goes, but I think both the true and false parts of the IIF() are evaluated.
If you do this in the immediate pane...

? iif(true, msgbox(true), msgbox(false))

...you get BOTH message boxes.

 

[boblarson]

That might still fail. See how it goes, but I think both the true and false parts of the IIF() are evaluated.
If you do this in the immediate pane...

? iif(true, msgbox(true), msgbox(false))

...you get BOTH message boxes.

I've used the IIF to set the calculation with no problem before. It works just fine. You check to see if the divisor is 0 (or not 0) and then select which item you want if it is 0.

 

[MarkK]

Yeah, you can use the IIF() to select a value like ...

IIF(value > 0, value, -1)

but if one of the expressions is the division by zero expression it will be evaluated and cause an error, like ...

IIF(value > 0, amount / value, -1)

... then 'amount / value' will still be evaluated if value = 0.
I think. Check it 'n' see.

 

[boblarson]

Yeah, you can use the IIF() to select a value like ...

IIF(value > 0, value, -1)

but if one of the expressions is the division by zero expression it will be evaluated and cause an error, like ...

IIF(value > 0, amount / value, -1)

... then 'amount / value' will still be evaluated if value = 0.
I think. Check it 'n' see.

NO it won't - YOU try it and see... I do it ALL the time.

 

[MarkK]

Try this in your immediate pane ...

? IIF(false, 1/0, 999)

... and see what you get.
I'm just sayin'...

 

[boblarson]

Try this in your immediate pane ...

? IIF(false, 1/0, 999)

... and see what you get.
I'm just sayin'...

You aren't doing it correctly. If you do up a CORRECT Formula - like this one I did in my test database:

SELECT IIf([Taxes]=0,0,[Shipping Fee]/[Taxes]) AS Amt
FROM Orders;


It works just fine. Your sample is set up to fail because you aren't checking the value of the divisor and then using something different based on the divisor you are checking something as false and then expecting it to act correctly which it won't. Try a REAL scenario like I've posted here and you will see it works just fine.

 

[MarkK]

Yup, I confirm that expression will succeed in SQL. Thanks Bob.
Cheers,

 

[ChrisO]

The IIf function behaves differently in VBA than in a query.

VBA reports the error, the query doesn’t.