Division by zero error

Posted on 2014-08-28
Last Modified: 2014-09-02
Why am I getting a Division by zero error with this code:

Me.txtACV = Me.txtTotalEstimatedReplacementCost * (Me.txtUsefulLife - Me.txtCurrentLifeOfAsset) / Me.txtUsefulLife
Question by:SteveL13
    LVL 26

    Expert Comment

    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.
    LVL 119

    Expert Comment

    by:Rey Obrero
    try this

    Me.txtACV = IIF(nz(Me.txtUsefulLife,0)=0,0,Me.txtTotalEstimatedReplacementCost * (Me.txtUsefulLife - Me.txtCurrentLifeOfAsset) / Me.txtUsefulLife)

    Author Comment

    Didn't work.  Still get div by zero error
    LVL 48

    Accepted Solution

    Try this:

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

    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    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)

    LVL 33

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now