Using IIf for handling divide by zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.
 
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.
 
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
 
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.
 
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
 
Back
Top