Using IIf for handling divide by zero

K

ken

I am trying to write an expression to calculate the percentage difference
between two numbers and list it in a query.

My problem is that some of my values are zero. Since I can’t divide by zero
I thought I would write an expression to give me a default value, such as 0%

For example if I had two zero amounts then I would just return a zero.

If I have two amounts greater than zero I would return the percent difference.

If I was trying to divide a number greater than zero by a zero, then I need
to return that value. E.g. 5000/0 = 5000

I tried using the IIF conditional expression, but I can only have two cases.

Any advice would be appreciated.

Ken
 
J

John Spencer

You can nest IIF statements (I believe up to 5 levels)

IIF( A = 0 and B = 0, 0, IIF ( B=0, A, A/B))

You didn't t
 
K

ken

Thank You John,

That worked perfectly. I was thinking along the lines of an else statement,
but had found nothing. I had not thought about nesting the IIF statement.

Is there a good reference for syntax I can use in the future? For example,
the Access help does not mention the ability to nest IIf statements.

Thank you again.
 
J

Jim Burke in Novi

In your case you don't even need the nested Iif. Just use IIF ( B=0, A, A/B).
If both A and B = 0, 0 will be returned since in that case you return the
value of A. And if only A=0 it wil divide 0 by B and return 0. There are many
good VBA reference books out there. I have 'Beginning Access 2000 VBA' by
SMith and Sussman, and it's been very helpful. Though it's a 'Beginning' book
it has a lot of detail in it. Another one that has a lot of great tips in it
is The Access 2000 Developers Handbook (Gitz/Litwin, Gilbert). It has a lot
of more advanced stuff in it. Between the two of those I've found most of the
info I've needed.
 
T

Tim Ferguson

Just use IIF ( B=0, A, A/B)
Click to expand...

Unfortunately all parts of the expression are evaluated so this will
still fail if b=0 (this is documented in the help file). That is why this
doesn't work either:

myIntegerVariable = Iif(isnull(a), 0, a)

I'm afraid there is only a Public Function solution like

public function SafeDiv(a as single, b as single) as single
if b = 0 Then
SafeDiv=0
else
SafeDiv=a/b
End if
End function

or, probably safer, using an On Error Resume Next catch, in case b >0 but
very very small (try this with b set to 1e-22222222222222223 for
example!).

All the best


Tim F
 
J

John Spencer

Tim,

That will work in a query. It will fail in VBA.

I think it is a matter of SQL handling the IIF differently than VBA IIF.
 
T

Tim Ferguson

I think it is a matter of SQL handling the IIF differently than VBA
IIF.
Click to expand...

Public Function SafeDiv(a As Single, b As Single) As Single
Dim rs As Recordset
Dim db As Database
Dim selectCommand As String

selectCommand = _
"SELECT IIF(" & b & "=0,0," & a & "/" & b & ") AS ANSWER"
Debug.Assert vbYes = MsgBox( _
selectCommand, vbQuestion Or vbYesNo, "Is this okay?")

Set db = CurrentDb()
Set rs = db.OpenRecordset(selectCommand, dbOpenSnapshot,
dbForwardOnly) SafeDiv = CSng(rs!Answer)

rs.Close
End Function


Well, 'pon my word, 'tis true. Thanks for that.

Tim F
 
Top