Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Sep 2010
    Posts
    32

    Unanswered: using an iif in microsoft access

    I am attempting to create 2 fields that will create decimals so that I can take these decimals and create a percentage in a new field.

    Here is what I have and I can't seem to get the errors gone.

    Code:
    WOAVG = IIf("[WOIssued]=0", "", Val(Nz(WOCompleted, 0)) / Val(Nz(WOIssued, 0)))
    
    PMSAVG = IIf("[PMSIssued]=0", "", Val(Nz(PMSCompleted, 0)) / Val(Nz(PMSIssued, 0)))
    basically I want these fields to calculate nothing if the field issued field is 0 but if it reads any number other than 0 I want it to divide the completed tasks by the issued tasks

    Please advice how I should rewrite this.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You don't want quotes around the test:

    IIf([WOIssued]=0, "", Val(Nz(WOCompleted, 0)) / Val(Nz(WOIssued, 0)))

    You'd want to move your Nz() function to the test. As it is, a Null value would result in a divide by zero error.
    Paul

  3. #3
    Join Date
    Sep 2010
    Posts
    32
    I am now receiving the following error not sure how to resolve it

    "The Expression After update you entered as the event property setting produced the following error: Overflow"

    Please advise

  4. #4
    Join Date
    Sep 2010
    Posts
    32
    Here is the event code:

    Code:
    Function sumUpdate()
    
    WOAVG = IIf([WOIssued] = 0, "", Val(Nz(WOCompleted, 0)) / Val(Nz(WOIssued, 0)))
    
    PMSAVG = IIf([PMSIssued] = 0, "", Val(Nz(PMSCompleted, 0)) / Val(Nz(PMSIssued, 0)))
    
    TOTALPERCENT = (([WOAVG] + [PMSAVG]) / 2)
    
    WOBacklog = Val(Nz(WOIssued, 0)) - Val(Nz(WOCompleted, 0))
    
    PMSBacklog = Val(Nz(PMSIssued, 0)) - Val(Nz(PMSCompleted, 0))
    
    
    TOTALPLANNED = Val(Nz(WOHours, 0)) + Val(Nz(PMSHours, 0)) + Val(Nz(SOCHours, 0)) + Val(Nz(PJTHours, 0))
    
    TOTALAVAIL = Val(Nz(TOTALPLANNED, 0)) + Val(Nz(DINHours, 0))
    
    End Function

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    On what line? What are the data types of the variables, and what values would be going into them?
    Paul

  6. #6
    Join Date
    Sep 2010
    Posts
    32
    I got it worked out now I am trying to get the average to work properly.

    Right now the Event code looks like this
    Code:
    Function sumUpdate()
    
    WOBacklog = Val(Nz(WOIssued, 0)) - Val(Nz(WOCompleted, 0))
    
    PMSBacklog = Val(Nz(PMSIssued, 0)) - Val(Nz(PMSCompleted, 0))
    
    TOTALPLANNED = Val(Nz(WOHours, 0)) + Val(Nz(PMSHours, 0)) + Val(Nz(SOCHours, 0)) + Val(Nz(PJTHours, 0))
    
    TOTALAVAIL = Val(Nz(TOTALPLANNED, 0)) + Val(Nz(DINHours, 0))
    
    WOAVG = IIf([WOIssued] = 0, 0, Val(Nz([WOCompleted], 0)) / Val(Nz([WOIssued], 0)))
    
    PMSAVG = IIf([PMSIssued] = 0, 0, Val(Nz([PMSCompleted], 0)) / Val(Nz([PMSIssued], 0)))
    
    TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PMSAVG], 0))) / 2)
    
    End Function
    Which the TOTALPERCENT section works unless one of the values are zero which will happen all the time. So I am not sure how to rewrite it so it works with zeros

    For example I have it set up so if 4 WO are issued and 3 are Completed it will give a .75 result in the WOAVG field
    Now if no PMS are issued then the TOTAL PERCENT event is trying to take (.75 + 0) /2 which won't give the 75% I want it to but instead give an error. This is getting frustrating lol

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you get this sorted out? I wouldn't expect an error, but an "incorrect" result. As I see it you have 4 possibilities, and you need to code for each:

    A and B both have a zero value (use zero)
    A has a value, B has zero (use A value)
    A has a zero, B has a value (use B value)
    A and B both have values (add them up and divide by 2)

    I'd likely use If/ElseIf to handle the various possibilities.
    Paul

  8. #8
    Join Date
    Sep 2010
    Posts
    32
    I have never wrote one of those would you mind doing one of the four so I can see how its structured.

    Thanks

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Code:
    If WOAVG = 0 AND PMSAVG = 0 Then
      TOTALPERCENT = 0
    ElseIf WOAVG <> 0 AND PMSAVG = 0 Then
      TOTALPERCENT = WOAVG
    ElseIf...
    
    End If
    Paul

  10. #10
    Join Date
    Sep 2010
    Posts
    32
    Do I put that in my Function sumUpdate() area?

    Sorry This is actually the first think I have ever built in Access
    I have experience in php but this is quite different

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Yes; it would replace the single line currently used. You could come up with a more complicated single line solution (nested IIf() functions), but I'd do the above for readability.
    Paul

  12. #12
    Join Date
    Sep 2010
    Posts
    32
    Thanks a lot

    How does this look (there is a third Average now)

    Code:
    Function sumUpdate()
    
    WOBacklog = Val(Nz(WOIssued, 0)) - Val(Nz(WOCompleted, 0))
    
    PMSBacklog = Val(Nz(PMSIssued, 0)) - Val(Nz(PMSCompleted, 0))
    
    TOTALPLANNED = Val(Nz(WOHours, 0)) + Val(Nz(PMSHours, 0)) + Val(Nz(SOCHours, 0)) + Val(Nz(PJTHours, 0))
    
    TOTALAVAIL = Val(Nz(TOTALPLANNED, 0)) + Val(Nz(DINHours, 0))
    
    WOAVG = IIf([WOIssued] = 0, 0, Val(Nz([WOCompleted], 0)) / Val(Nz([WOIssued], 0)))
    
    PMSAVG = IIf([PMSIssued] = 0, 0, Val(Nz([PMSCompleted], 0)) / Val(Nz([PMSIssued], 0)))
    
    PJTAVG = IIf([PJTIssued] = 0, 0, Val(Nz([PJTCompleted], 0)) / Val(Nz([PJTIssued], 0)))
    
    If [WOAVG] = 0 And [PMSAVG] = 0 And [PJTAVG] = 0 Then
      TOTALPERCENT = 0
    ElseIf [WOAVG] <> 0 And [PMSAVG] = 0 And [PJTAVG] = 0 Then
      TOTALPERCENT = [WOAVG]
    ElseIf [PMSAVG] <> 0 And [WOAVG] = 0 And [PJTAVG] = 0 Then
      TOTALPERCENT = [PMSAVG]
    ElseIf [PJTAVG] <> 0 And [WOAVG] = 0 And [PMSAVG] = 0 Then
      TOTALPERCENT = [PJTAVG]
    ElseIf [WOAVG] <> 0 And [PMSAVG] <> 0 And [PJTAVG] = 0 Then
      TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PMSAVG], 0))) / 2)
    ElseIf [WOAVG] <> 0 And [PMSAVG] = 0 And [PJTAVG] <> 0 Then
      TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PJTAVG], 0))) / 2)
    ElseIf [WOAVG] = 0 And [PMSAVG] <> 0 And [PJTAVG] <> 0 Then
      TOTALPERCENT = ((Val(Nz([PMSAVG], 0)) + Val(Nz([PJTAVG], 0))) / 2)
    ElseIf [WOAVG] <> 0 And [PMSAVG] <> 0 And [PJTAVG] <> 0 Then
      TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PMSAVG], 0)) + Val(Nz([PJTAVG], 0))) / 3)
    End If
    
    ' TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PMSAVG], 0)) + Val(Nz([PJTAVG], 0))) / 3)
    End Function

    EDIT: I just tryed that and it says Overflow so I assume I did something wrong
    Last edited by Abbernacki; 09-15-10 at 13:37.

  13. #13
    Join Date
    Sep 2010
    Posts
    32
    ok I found that this code works

    Code:
    Function sumUpdate()
    
    WOBacklog = Val(Nz(WOIssued, 0)) - Val(Nz(WOCompleted, 0))
    
    PMSBacklog = Val(Nz(PMSIssued, 0)) - Val(Nz(PMSCompleted, 0))
    
    TOTALPLANNED = Val(Nz(WOHours, 0)) + Val(Nz(PMSHours, 0)) + Val(Nz(SOCHours, 0)) + Val(Nz(PJTHours, 0))
    
    TOTALAVAIL = Val(Nz(TOTALPLANNED, 0)) + Val(Nz(DINHours, 0))
    
    WOAVG = IIf([WOIssued] = 0, 0, Val(Nz([WOCompleted], 0)) / Val(Nz([WOIssued], 0)))
    
    PMSAVG = IIf([PMSIssued] = 0, 0, Val(Nz([PMSCompleted], 0)) / Val(Nz([PMSIssued], 0)))
    
    PJTAVG = IIf([PJTIssued] = 0, 0, Val(Nz([PJTCompleted], 0)) / Val(Nz([PJTIssued], 0)))
    
    If [WOAVG] = "0" And [PMSAVG] = "0" And [PJTAVG] = "0" Then
      TOTALPERCENT = "0"
      
    ElseIf [WOAVG] <> "0" And [PMSAVG] = "0" And [PJTAVG] = "0" Then
      TOTALPERCENT = [WOAVG]
      
    ElseIf [PMSAVG] <> "0" And [WOAVG] = "0" And [PJTAVG] = "0" Then
      TOTALPERCENT = [PMSAVG]
      
    ElseIf [PJTAVG] <> "0" And [WOAVG] = "0" And [PMSAVG] = "0" Then
      TOTALPERCENT = [PJTAVG]
      
    ElseIf [WOAVG] <> "0" And [PMSAVG] <> "0" And [PJTAVG] = "0" Then
      TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PMSAVG], 0))) / 2)
      
    ElseIf [WOAVG] <> "0" And [PMSAVG] = "0" And [PJTAVG] <> "0" Then
      TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PJTAVG], 0))) / 2)
      
    ElseIf [WOAVG] = "0" And [PMSAVG] <> "0" And [PJTAVG] <> "0" Then
      TOTALPERCENT = ((Val(Nz([PMSAVG], 0)) + Val(Nz([PJTAVG], 0))) / 2)
      
    ElseIf [WOAVG] <> "0" And [PMSAVG] <> "0" And [PJTAVG] <> "0" Then
      TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PMSAVG], 0)) + Val(Nz([PJTAVG], 0))) / 3)
      
    End If
    
    'TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PMSAVG], 0)) + Val(Nz([PJTAVG], 0))) / 3)
    End Function
    but the problem is when WOAVG, PMSAVG or PJTAVG is 0 I am getting the overflow error which is bugging me cause it worked fine yesterday as written.
    Any suggestions?

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I don't think you want quotes around any of the zeros, but if that is a numeric variable (or field) using them would cause the error. Try this:

    TOTALPERCENT = 0
    Paul

  15. #15
    Join Date
    Sep 2010
    Posts
    32
    I removed all the quotes, there is no more overflow but it doesn't calculate the AVG anymore

    Code:
    Function sumUpdate()
    
    WOBacklog = Val(Nz(WOIssued, 0)) - Val(Nz(WOCompleted, 0))
    
    PMSBacklog = Val(Nz(PMSIssued, 0)) - Val(Nz(PMSCompleted, 0))
    
    TOTALPLANNED = Val(Nz(WOHours, 0)) + Val(Nz(PMSHours, 0)) + Val(Nz(SOCHours, 0)) + Val(Nz(PJTHours, 0))
    
    TOTALAVAIL = Val(Nz(TOTALPLANNED, 0)) + Val(Nz(DINHours, 0))
    
    WOAVG = IIf([WOIssued] = 0, 0, Val(Nz([WOCompleted], 0)) / Val(Nz([WOIssued], 0)))
    
    PMSAVG = IIf([PMSIssued] = 0, 0, Val(Nz([PMSCompleted], 0)) / Val(Nz([PMSIssued], 0)))
    
    PJTAVG = IIf([PJTIssued] = 0, 0, Val(Nz([PJTCompleted], 0)) / Val(Nz([PJTIssued], 0)))
    
    If [WOAVG] = 0 And [PMSAVG] = 0 And [PJTAVG] = 0 Then
      TOTALPERCENT = 0
      
    ElseIf [WOAVG] <> 0 And [PMSAVG] = 0 And [PJTAVG] = 0 Then
      TOTALPERCENT = [WOAVG]
      
    ElseIf [PMSAVG] <> 0 And [WOAVG] = 0 And [PJTAVG] = 0 Then
      TOTALPERCENT = [PMSAVG]
      
    ElseIf [PJTAVG] <> 0 And [WOAVG] = 0 And [PMSAVG] = 0 Then
      TOTALPERCENT = [PJTAVG]
      
    ElseIf [WOAVG] <> 0 And [PMSAVG] <> 0 And [PJTAVG] = 0 Then
      TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PMSAVG], 0))) / 2)
      
    ElseIf [WOAVG] <> 0 And [PMSAVG] = 0 And [PJTAVG] <> 0 Then
      TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PJTAVG], 0))) / 2)
      
    ElseIf [WOAVG] = 0 And [PMSAVG] <> 0 And [PJTAVG] <> 0 Then
      TOTALPERCENT = ((Val(Nz([PMSAVG], 0)) + Val(Nz([PJTAVG], 0))) / 2)
      
    ElseIf [WOAVG] <> 0 And [PMSAVG] <> 0 And [PJTAVG] <> 0 Then
      TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PMSAVG], 0)) + Val(Nz([PJTAVG], 0))) / 3)
      
    End If
    
    'TOTALPERCENT = ((Val(Nz([WOAVG], 0)) + Val(Nz([PMSAVG], 0)) + Val(Nz([PJTAVG], 0))) / 3)
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •