Division by Zero Error

Discussion in 'Microsoft Access Queries' started by clk, Jun 18, 2012.

  1. clk

    clk Guest

    I am trying to make a summary query to show the percent of change in
    test scores. I have it working except when a zero is entered.

    In my query I have the following two fields:

    PercentMoney: IIf([LegalCorrect]=0,0,[LegalCorrect]/[Legal])

    PctGain: IIf(IsNull([PrevDate]) Or NZ([PercentMoney]=0),0,
    (([PercentMoney]/
    NZ(DLookUp("PercentMoney","qryDanielMemorialScoresLegalSummary","DMDate=
    #" & [PrevDate] & "#"),0)))-1)*100

    I thought I was handling the division by zero errors but it is still
    throwing that error message.

    Any help would be greatly appreciated.

    Thank you.
     
    clk, Jun 18, 2012
    #1
    1. Advertisements

  2. clk

    Rob Parker Guest

    To use an IIf function to prevent a division by zero error, you need to test
    the term which is the divisor, not the dividend. So, for example, your
    first expression needs to be something like:

    PercentMoney: IIf([Legal]=0,0,[LegalCorrect]/[Legal])

    or maybe, depending on exactly what you're trying to achieve:
    PercentMoney: IIf([Legal]=0,[LegalCorrect],[LegalCorrect]/[Legal])
    or maybe something else - I don't understand exactly what you're trying to
    accomplish here.

    HTH,

    Rob

    BTW: the term NZ([PercentMoney]=0) in your expression for PctGain seems
    strange; it will return 0 for every value of PercentMoney except Null, and
    will return Null if PercentMoney is Null. Perhaps you want
    nz([PercentMoney],0), which will return the actual value of PercentMoney
    except when it is null, in which case it will return 0.

    "clk" <> wrote in message
    news:...
    >I am trying to make a summary query to show the percent of change in
    > test scores. I have it working except when a zero is entered.
    >
    > In my query I have the following two fields:
    >
    > PercentMoney: IIf([LegalCorrect]=0,0,[LegalCorrect]/[Legal])
    >
    > PctGain: IIf(IsNull([PrevDate]) Or NZ([PercentMoney]=0),0,
    > (([PercentMoney]/
    > NZ(DLookUp("PercentMoney","qryDanielMemorialScoresLegalSummary","DMDate=
    > #" & [PrevDate] & "#"),0)))-1)*100
    >
    > I thought I was handling the division by zero errors but it is still
    > throwing that error message.
    >
    > Any help would be greatly appreciated.
    >
    > Thank you.
     
    Rob Parker, Jun 19, 2012
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chip Melton

    Division by Zero Error

    Chip Melton, Aug 1, 2003, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    167
    Ken Snell
    Aug 2, 2003
  2. Guest

    QRY Formula - 'Division by Zero' Error

    Guest, Jan 17, 2005, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    494
    Guest
    Jan 17, 2005
  3. Calvin Willman

    Division by Zero Error

    Calvin Willman, Jan 12, 2006, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    340
    John Spencer
    Jan 12, 2006
  4. Guest

    division is zero error in query

    Guest, Mar 21, 2006, in forum: Microsoft Access Queries
    Replies:
    7
    Views:
    163
    Tom Ellison
    Mar 22, 2006
  5. Jasper Recto

    Division by zero error

    Jasper Recto, Jul 30, 2008, in forum: Microsoft Access Queries
    Replies:
    16
    Views:
    578
    strive4peace
    Aug 1, 2008
Loading...

Share This Page