• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 140
  • Last Modified:

Division by zero error

Why am I getting a Division by zero error with this code:

Me.txtACV = Me.txtTotalEstimatedReplacementCost * (Me.txtUsefulLife - Me.txtCurrentLifeOfAsset) / Me.txtUsefulLife
2 Solutions
What are the values of the textboxes when running the code. I believe the error message tells you exactly what the problem is, your dividing a number by 0.
Rey Obrero (Capricorn1)Commented:
try this

Me.txtACV = IIF(nz(Me.txtUsefulLife,0)=0,0,Me.txtTotalEstimatedReplacementCost * (Me.txtUsefulLife - Me.txtCurrentLifeOfAsset) / Me.txtUsefulLife)
SteveL13Author Commented:
Didn't work.  Still get div by zero error
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Gustav BrockCIOCommented:
Try this:

If IsNull(Me.txtUsefulLife) Then
    Me.txtACV = 0
    Me.txtACV = Me.txtTotalEstimatedReplacementCost * (Me.txtUsefulLife - Me.txtCurrentLifeOfAsset) / Me.txtUsefulLife
End If

Dale FyeCommented:
I agree with Gustav.

The IIF() function will evaluate the left argument, but will also evaluate both the true and false agruments.  So you need to split this out into an If Then Else End If construct. You can see this behavior with:

?iif(true, 1, 1/0)

Dale is correct but interestingly, the IIf() works differently in VBA than it does in SQL.  In VBA, all clauses of the IIf() are evaluated and so you get the divide by zero error whether it is in the true path or the false path but if you used the same expression in a query, only the true path is evaluated so you would not get the divide by zero error.  Because of this behavior, I almost never use the IIf() in code.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now