Results 1 to 15 of 24
Thread: using an iif in microsoft access

091310, 17:14 #1Registered User
 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)))
Please advice how I should rewrite this.

091310, 17:20 #2Registered User
 Join Date
 May 2005
 Location
 Nevada, USA
 Posts
 2,888
Provided Answers: 6You 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

091410, 08:54 #3Registered User
 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

091410, 09:00 #4Registered User
 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

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

091410, 12:03 #6Registered User
 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
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

091410, 19:55 #7Registered User
 Join Date
 May 2005
 Location
 Nevada, USA
 Posts
 2,888
Provided Answers: 6Did 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

091510, 08:36 #8Registered User
 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

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

091510, 13:10 #10Registered User
 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

091510, 13:19 #11Registered User
 Join Date
 May 2005
 Location
 Nevada, USA
 Posts
 2,888
Provided Answers: 6Yes; 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

091510, 13:22 #12Registered User
 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 wrongLast edited by Abbernacki; 091510 at 13:37.

091510, 14:10 #13Registered User
 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
Any suggestions?

091510, 14:13 #14Registered User
 Join Date
 May 2005
 Location
 Nevada, USA
 Posts
 2,888
Provided Answers: 6I 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 = 0Paul

091510, 14:20 #15Registered User
 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