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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Chip Melton

    Division by Zero Error

    Chip Melton, Aug 1, 2003, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    366
    Ken Snell
    Aug 2, 2003
  2. Alan B. Densky

    Totals Query And Division By Zero

    Alan B. Densky, Jan 22, 2004, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    396
    Alan B. Densky
    Jan 22, 2004
  3. Guest

    QRY Formula - 'Division by Zero' Error

    Guest, Jan 17, 2005, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    663
    Guest
    Jan 17, 2005
  4. Guest

    Division by Zero

    Guest, May 30, 2005, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    289
    Michel Walsh
    May 30, 2005
  5. Calvin Willman

    Division by Zero Error

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

    division is zero error in query

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

    Division by zero error

    Jasper Recto, Jul 30, 2008, in forum: Microsoft Access Queries
    Replies:
    16
    Views:
    713
    strive4peace
    Aug 1, 2008
  8. Tray

    Division By Zero error

    Tray, Oct 29, 2008, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    263
    John Spencer
    Oct 29, 2008
Loading...