Results 1 to 13 of 13
  1. #1
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84

    #Num! Error and #Div/0!

    I have tried several different ways of getting rid of the #num! and #div/0! errors and it did not work. Her is the root of the sql. Can any one help me receive the % of the following and not show the errors.



    Test: [Tracker SubTotal]/[Budget Total]-1

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    #div/0 indicates division by zero

    so the value of [Budget Total]-1 must be 0 somewhere in the calculation

    You could test using iif(expression, true, False)

    TEST: IIF ( [Budget Total] = 1,0,[Tracker SubTotal]/[Budget Total]-1)

  3. #3
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    Still receive Error

    Click image for larger version. 

Name:	table5.GIF 
Views:	10 
Size:	187.4 KB 
ID:	25191

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may have some NULL values. Try this

    TEST: IIF ( [Budget Total] = 1,0, NZ([Tracker SubTotal],0)/[Budget Total]-1)

  5. #5
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    Nope didnt work but this did, but i am still not getting the right number. For instance why is there a 700 and 1.000xxxxxxx etc. This column will be a percentage section in my report

    TEST1: IIf([Budget Total]=1,0,CDbl(Nz([Tracker SubTotal],0)/CDbl(Nz([Budget Total]-1))))

    Click image for larger version. 

Name:	A1.GIF 
Views:	11 
Size:	179.6 KB 
ID:	25192

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by GCLIFTON View Post
    Test: [Tracker SubTotal]/[Budget Total]-1
    If this is your formula, due to order of precedence (divide first, then subtract), it is saying to divide [Tracker SubTotal] by [Budget Total], THEN subtract 1.

    Do you want
    Test: ([Tracker SubTotal]/[Budget Total]) -1
    or
    Test: [Tracker SubTotal]/([Budget Total]-1)

    ????

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by GCLIFTON View Post
    Nope didnt work but this did, but i am still not getting the right number. For instance why is there a 700 and 1.000xxxxxxx etc. This column will be a percentage section in my report

    TEST1: IIf([Budget Total]=1,0,CDbl(Nz([Tracker SubTotal],0)/CDbl(Nz([Budget Total]-1))))
    --------------
    OK, the 700.

    Lets say
    [Budget Total] = 0 and
    [Tracker SubTotal] = -700 (I REALLY hate spaces in names!!)

    Putting the values in the formula results in
    =IIf([Budget Total]=1,0,CDbl(Nz([Tracker SubTotal],0)/CDbl(Nz([Budget Total]-1))))
    =IIf([0]=1,0,CDbl(Nz([-700],0)/CDbl(Nz([0]-1))))

    Doing the math results in
    =IIf([0]=1,0,CDbl(Nz([-700],0)/CDbl(-1))))

    which is
    =CDbl(Nz([-700],0)/CDbl(-1)))

    getting rid of the un-necessary
    = -700/-1

    which equals 700.


    --------------
    Now the 1.0001542738151

    [Budget Total] = 6482.9814 and
    [Tracker SubTotal] = 6482.9814

    Putting the values in the formula results in
    =IIf([Budget Total]=1,0,CDbl(Nz([Tracker SubTotal],0)/CDbl(Nz([Budget Total]-1))))
    =IIf([0]=1,0,CDbl(Nz([6482.9814],0)/CDbl(Nz([6482.9814]-1))))

    Doing the math results in
    =IIf([0]=1,0,CDbl(Nz([6482.9814],0)/CDbl(6481.9814))))

    which is
    =CDbl(Nz([6482.9814],0)/CDbl(6481.9814)))

    getting rid of the un-necessary
    = 6482.9814 / 6481.9814

    which equals 1.0001542738151


    For the 700 issue, maybe try
    Code:
    TEST1: IIf([Budget Total]=0 or [Budget Total]=1,0,CDbl(Nz([Tracker SubTotal],0)/CDbl(Nz([Budget Total]-1))))

  8. #8
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    ok i see what you mean now. And i agree i dont like space as well. I always go back and put _ in. But the reason i was getting the wrong values was because i was dividing by the wrong values. Had a moment. My apologies Everything is working perfectly now. But wow i like how you spelled that out. I am going to use this as well.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help....

  10. #10
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    wait i just reviewed further down and i am incorrect. I cant figure this out
    This is what i have

    Percentage: IIf([Budget_Total]=1,0,[SC_Tracker_SubTotal]/[Budget_Total])

    I just want to know what is the percentage of the [SC_TRACKER_SUBTOTAL] is to [Budget_Total]. So like 1932-3 should reflect roughly 97%

    Click image for larger version. 

Name:	a2.GIF 
Views:	8 
Size:	92.3 KB 
ID:	25193

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'd start with

    Percentage: IIf([Budget_Total]=1,0,([SC_Tracker_SubTotal]/[Budget_Total])*100)

  12. #12
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    Ok how do i write this without the *100 because in the Report i have that column formatted to % and i apologize it should say

    Percentage: IIf([Budget_Total]=1,0,([Tracker_Total]/[Budget_Total])*100) . So for that same example 1932-3 it should equal .02 or 2% with the formatting


  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would try (at least it works for me... 2.25%)
    Code:
    Percentage: IIf([Budget_Total]=0,0,([Tracker_Total]/[Budget_Total]))
    Notice the zero, since you don't want the divide by zero error.

    (I did get a little lost with all of the field changes )

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-17-2016, 02:10 PM
  2. Replies: 2
    Last Post: 01-04-2016, 09:40 AM
  3. Replies: 3
    Last Post: 01-23-2014, 07:49 AM
  4. Replies: 0
    Last Post: 07-16-2012, 05:42 AM
  5. Replies: 6
    Last Post: 05-30-2012, 12:32 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums