Help with syntax IIF statement

Can you please rephrase that?
You want to change the part after the / (divide)?
It seems to already have a condition for DateRequired<=Date()
Do you mean this?

IIf(
      Sum(IIf([CompletionDt]<=[DateRequired],1,0))=0,
      0,
      Sum(IIf([CompletionDt]<=[DateRequired],1,0))
      /
      (Sum(IIf([CompletionDt]<=[DateRequired],1,0))
      +Sum(IIf([CompletionDt]>[DateRequired],1,0))
      +Sum(IIf(IsNull(CompletionDt) OR [DateRequired]<Date(),1,0)))
      )

Can you give us a plain English description of what you are trying to calculate with the two date fields?

Not quite.  I am looking for the Sum of Percentage complete OnTime per Manager by calculating the individual employees' Percentage Complete OnTime. So I need the current equation to work as is but not include any employee's Courses that are in the future or have not completed as of current date as part of the summation.

Say Employee 1 has been assigned 5 courses over the next year - of those 5 courses 1 has been CompletedOntime 1 was CompletedLate and 1 CompletedDelinquent as of current date and the remaining 2 are in the future - so the Pct = ?% of OnTime

CompletedOntime:        Sum(IIf([CompletionDt]<=[DateRequired],1,0))    +
CompletedLate:             Sum(IIf([CompletionDt]>[DateRequired],1,0))  +
CompletedDelinquent: Sum(IIf([DateRequired]<Date(),1,0)

Hope this explains what I am trying to accomplish.

Karen

Just a note here:

When your formulas get this complex, please consider using Functions instead.

Remember:
You can't add comments to Formulas
You can't add comments to Formulas
...but you can do both and more with Functions.

SELECT MgrID,
-Sum(CompletionDt<=DateRequired) AS OnTime,
-Sum(CompletionDt>DateRequired) as CompLate,
-Sum(DateRequired>Date()) AS InFuture,
OnTime/(OnTime+CompLate) as PctOnTime
FROM MgrCourses
GROUP BY MgrID

As you can see, I hate nested IIF()'s ;-)

"Remember:
You can't add comments to Formulas
You can't add comments to Formulas
...but you can do both and more with Functions."

I know that - it was to illustrate my point.

K

GRayL:

this is actual 1 field in an existing query.

K

cyberkiwi:

Your example - returns #Error.

K

You did not state how you wanted to treat the situation where the denominator was zero (divide by zero) error.

If all you want is the MgrID and his percent, then do a query of a query.

IIf(
      (Sum(IIf([CompletionDt]<=[DateRequired] AND [DateRequired]<=Date(),1,0))
      +Sum(IIf([CompletionDt]>[DateRequired] AND [DateRequired]<=Date(),1,0))
      +Sum(IIf(IsNull(CompletionDt) AND [DateRequired]<Date(),1,0)))=0,
      0,
      Sum(IIf([CompletionDt]<=[DateRequired] AND [DateRequired]<=Date(),1,0))
      /
      IIf((Sum(IIf([CompletionDt]<=[DateRequired] AND [DateRequired]<=Date(),1,0))
      +Sum(IIf([CompletionDt]>[DateRequired] AND [DateRequired]<=Date(),1,0))
      +Sum(IIf(IsNull(CompletionDt) AND [DateRequired]<Date(),1,0)))=0, 1,
      (Sum(IIf([CompletionDt]<=[DateRequired] AND [DateRequired]<=Date(),1,0))
      +Sum(IIf([CompletionDt]>[DateRequired] AND [DateRequired]<=Date(),1,0))
      +Sum(IIf(IsNull(CompletionDt) AND [DateRequired]<Date(),1,0)))))

No there are several other  fields  I need Percent On Time as of Current date by Empl(Bems).

K

SELECT qryUnitChief.UCBEMS, qryUnitChief.UnitChief, qryEmployeeManager.Org, qryEmployeeManager.MgrName, [tblEmployee].[LastName] & ", " & [tblEmployee].[Firstname] AS EmployeeName, tblEmployee.BEMS, tblCourseList_lkup.StandardRequiredDt AS MandReqDate, tblCourseList_lkup.[Ilp Learning Cd], qryEmpRequiredLearning.CompletionDt, IIf(Sum(IIf([CompletionDt]<=[DateRequired],1,0))=0,0,Sum(IIf([CompletionDt]<=[DateRequired],1,0))/(Sum(IIf([CompletionDt]<=[DateRequired],1,0))+Sum(IIf([CompletionDt]>[DateRequired],1,0))+Sum(IIf([DateRequired]<Date(),1,0)))) AS Pct, IIf(DateDiff("d",[ServiceDt],Date())<=60,"X","") AS NH, IIf(IsNull([xfrout]),IIf(DateDiff("d",[xfrIn],Date())<=60,"X","")) AS Transfers, IIf(IsNull([LOA_StartDate])=False And IsNull([LOA_EndDate]),"X","") AS LOA
FROM (((tblEmployee LEFT JOIN qryUnitChief ON tblEmployee.UnitChief = qryUnitChief.UnitChiefID) LEFT JOIN qryEmployeeManager ON tblEmployee.Mgr_OrgNo = qryEmployeeManager.OrgCtr) INNER JOIN qryEmpRequiredLearning ON tblEmployee.BEMS = qryEmpRequiredLearning.BEMS) INNER JOIN (tblCourseClassification_lkup INNER JOIN tblCourseList_lkup ON tblCourseClassification_lkup.ClassificationRecID = tblCourseList_lkup.ClassificationRecID) ON qryEmpRequiredLearning.CourseRecID = tblCourseList_lkup.CourseRecID
GROUP BY qryUnitChief.UCBEMS, qryUnitChief.UnitChief, qryEmployeeManager.Org, qryEmployeeManager.MgrName, [tblEmployee].[LastName] & ", " & [tblEmployee].[Firstname], tblEmployee.BEMS, tblCourseList_lkup.StandardRequiredDt, tblCourseList_lkup.[Ilp Learning Cd], qryEmpRequiredLearning.CompletionDt, IIf(DateDiff("d",[ServiceDt],Date())<=60,"X",""), IIf(IsNull([xfrout]),IIf(DateDiff("d",[xfrIn],Date())<=60,"X","")), IIf(IsNull([LOA_StartDate])=False And IsNull([LOA_EndDate]),"X",""), tblEmployee.LastName, tblEmployee.FirstName, tblEmployee.MidName, tblCourseList_lkup.[Ilp Learning Cd], tblCourseList_lkup.OnXLS, tblCourseList_lkup.InActive
HAVING (((tblCourseList_lkup.OnXLS)<>0) AND ((tblCourseList_lkup.InActive)<>-1))
ORDER BY tblEmployee.LastName, tblEmployee.FirstName, tblEmployee.MidName, tblCourseList_lkup.[Ilp Learning Cd];

Select allOpen in new window

cyberkiwi:

Your latest only returns Zero.

Thanks for your efforts,

K

GRayL:

Divided by Zero return Zero

Ok lets simplified this some - after talking with my client - we need to accomplish the following  To determine On Time (first step)

If the CompletionDate = Null then return 0
Else
if CompletionDate <= Required date the return 1

To calculate Percentage of OnTime then

Take the Sum(the 1s) and divide by the Sum of OnTime, Late, and Delinquent.

So what do I need to modify in my original equation to get this to happen?

karen

'These are the only possible success cases.
if CompletionDate is null and DateRequired => Date() then 1  (Can still be on time, but is in the future)
if CompletionDate <= DateRequired then 1                              (This is on time)
/
'These are all cases
If CompletionDate is null and DateRequired < Date() then 0    (This is late)
if CompletionDate is null and DateRequired => Date() then 1  (Can still be on time, but is in the future)
if CompletionDate <= DateRequired then 1                              (This is on time)
if CompletionDate > DateRequired then 0                                (This was late)

so logically wouldn't it look like so?


sum(
      ([CompletionDt]<=[DateRequired]) +
      (isnull(CompletionDate) = True and DateRequired => Date())
)
/
sum(
      ([CompletionDt]<=[DateRequired]) +
      (isnull(CompletionDate) = True and DateRequired => Date())
      ([CompletionDt]>[DateRequired]) +
      (isnull(CompletionDate) = True and DateRequired < Date())
)

J

Can you have a Manager with zero DateRequired?

Please look at my latest post for revision to my request.

K

Here is my latest attempt - get aggregate issue.

IIf(Not IsNull([CompletionDt]),[CompletionDt]<=[DateRequired],1)/(Sum(IIf(Not IsNull([CompletionDt]),[CompletionDt]<=[DateRequired],1)))+Sum(IIf(Not IsNull([CompletionDt]),[CompletionDt]>[DateRequired],1)+Sum(IIf([DateRequired]<Date(),1)))

Select allOpen in new window

Here is my latest attempt - I broke up the queries first to get the totals of OnTime, Late, & Delinquent,
then Summary query to calculate the percentages,  However, I am getting Divide by Zero errors.

What am I missing?

SELECT qryEmpTrain_TempSummary.UCBEMS, qryEmpTrain_TempSummary.UnitChief, qryEmpTrain_TempSummary.MgrName, qryEmpTrain_TempSummary.BEMS, qryEmpTrain_TempSummary.Org, qryEmpTrain_TempSummary.OnTime, qryEmpTrain_TempSummary.Late, qryEmpTrain_TempSummary.Del, Sum(([OnTime]+[Late]+[Del])) AS TotalCourseCt, IIf(Sum([Ontime]+[Late])<>0,([Ontime]+[Late])/([OnTime]+[Late]+[Del]),0) AS [Total%Completed], Sum(IIf(Not IsNull([OnTime]),[OnTime]/([OnTime]+[Late]+[Del]),0)) AS [%CompleteOnTime], Sum(IIf(Not IsNull([Late]),[Late]/([OnTime]+[Late]+[Del]),0)) AS [%CompleteLate], Sum(IIf(Not IsNull([Del]),[Del]/([OnTime]+[Late]+[Del]),0)) AS [%CompleteDel], qryEmpTrain_TempSummary.OrgCtr, qryEmpTrain_TempSummary.ManagerID
FROM qryEmpTrain_TempSummary
GROUP BY qryEmpTrain_TempSummary.UCBEMS, qryEmpTrain_TempSummary.UnitChief, qryEmpTrain_TempSummary.MgrName, qryEmpTrain_TempSummary.BEMS, qryEmpTrain_TempSummary.Org, qryEmpTrain_TempSummary.OnTime, qryEmpTrain_TempSummary.Late, qryEmpTrain_TempSummary.Del, qryEmpTrain_TempSummary.OrgCtr, qryEmpTrain_TempSummary.ManagerID;

Select allOpen in new window

correct the Divide by Zero error by change the Groupby to Expression instead of Sum

BuT

Now I am getting some values with a Negative number from the 1st query - What am I am missing?

Karen

Sum(IIf(Not IsNull([CompletionDt]),[CompletionDt]<=[DateRequired],1)) AS OnTime,
Sum(IIf(Not IsNull([CompletionDt]),[CompletionDt]>[DateRequired],1)) AS Late,
Sum(IIf([DateRequired]<Date(),1,0)) AS Del,

SELECT qryUnitChief.UCBEMS, tblEmployee.UnitChief, qryEmployeeManager.MgrName, tblEmployee.BEMS, qryEmployeeManager.Org, Sum(IIf(Not IsNull([CompletionDt]),[CompletionDt]<=[DateRequired],1)) AS OnTime, Sum(IIf(Not IsNull([CompletionDt]),[CompletionDt]>[DateRequired],1)) AS Late, Sum(IIf([DateRequired]<Date(),1,0)) AS Del, qryEmployeeManager.OrgCtr, qryEmployeeManager.ManagerID
FROM ((tblEmployee LEFT JOIN qryEmployeeManager ON tblEmployee.Mgr_OrgNo = qryEmployeeManager.OrgCtr) INNER JOIN qryUnitChief ON tblEmployee.UnitChief = qryUnitChief.UnitChiefID) INNER JOIN qryEmpRequiredLearning ON tblEmployee.BEMS = qryEmpRequiredLearning.BEMS
GROUP BY qryUnitChief.UCBEMS, tblEmployee.UnitChief, qryEmployeeManager.MgrName, tblEmployee.BEMS, qryEmployeeManager.Org, qryEmployeeManager.OrgCtr, qryEmployeeManager.ManagerID;

Select allOpen in new window

Thanks for your efforts - I have not had time to get back to this post or problem - pulled on another project.

I awarded the points for your time.