#Error Handling

I Need an extra pair of eyes, I just can see whats wrong with this equation.  If the results = 0 then I get a #Error.

 IIf(Sum(IIf([CompletionDt]<=[DateRequired],1,0))+Sum(IIf([CompletionDt]>[DateRequired],1,0))+Sum(IIf([DateRequired]>Date(),1,0))+Sum(IIf([DateRequired]<Date(),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))))

Need to add the IF = Zero return Zero - this is on a percentage field.  Maybe Divide by Zero issue.
K
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
Karen,

  Right, it only accepts two arguments, but what about:

   ReturnPercent([totalCourseCt],[OnTime]+[Late]+[Del])

JimD.
0
 
VTKeganCommented:
You can just use the Nz( <<your expression >>, 0)
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Still get #Error - Maybe it is the Divide by Zero issue.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Karen SchaeferBI ANALYSTAuthor Commented:
the first portion of my equation is suppose to check for Zero values - What am I missing unless it the divide by zero issue?


If([CompletionDt]<=[DateRequired],1,0))+Sum(IIf([CompletionDt]>[DateRequired],1,0))+Sum(IIf([DateRequired]>Date(),1,0))+Sum(IIf([DateRequired]<Date(),1,0))=0,0,
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
After further investigation the problem lies with the Division portion of the equation.  If the Date Required is in the Future then it returns the #Error.  

So if the Completion Date = 0 and the Date Required is in the Future what do I need to do to the equation.

 IIf(Sum(IIf([CompletionDt]<=[DateRequired],1,0))+Sum(IIf([CompletionDt]>[DateRequired],1,0))+Sum(IIf([DateRequired]>Date(),1,0))+Sum(IIf([DateRequired]<Date(),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))))
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  #Error is not a null.  It either an error or no records.

  Paste the attached code into a standard module, then do:

= AvoidError(IIf(Sum(IIf([CompletionDt]<=[DateRequired],1,0))+Sum(IIf([CompletionDt]>[DateRequired],1,0))+Sum(IIf([DateRequired]>Date(),1,0))+Sum(IIf([DateRequired]<Date(),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)))),0)

  However depending on where your using this, you may need to do it a bit differently.  How/where are you using this expression?

 Second, I would suggest building a UDF (User Defined Function) for this to return the value rather then using all the IIF().

 First, it's very hard to debug and/or modify.  Second, the drawback with IIF() is that both the true and false arguments are evaluated (and this may be where your error is coming in).

  In a UDF, that doesn't happen, plus you get error trapping and it may be a tad faster (depends on a number of factors)

JimD.

Function AvoidError(n As Variant, varReplaceWith As Variant)
         
    On Error GoTo AvoidError_Error
    
    AvoidError = Nz(n, varReplaceWith)
    
AvoidError_Exit:
    Exit Function
 
AvoidError_Error:
    AvoidError = varReplaceWith
    Resume AvoidError_Exit
    
End Function

Open in new window

0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Second, I would suggest building a UDF (User Defined Function) for this to return the value rather then using all the IIF().

I included your function, however, I am still getting the Error Msg.  I included your function within my query.

How would I go about that.  I have other equations within the query that have no problems using something very similar.   A few months back I attempted to write some functions to handle this without success, so I reverted back to the IIF statements.

Using the equation above what would you recommend.

K
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Here is my entire query.


SELECT qryEmpInfo.UnitChief, qryEmpInfo.Mgr_OrgNo, qryEmpInfo.BEMS, [LastName] & ", " & [FirstName] AS Employee, Sum(IIf([CompletionDt]<=[DateRequired],1,0)) AS OnTime, Sum(IIf([CompletionDt]>[DateRequired],1,0)) AS Late, Sum(IIf([DateRequired]<Date(),1,0)) AS Del, Sum(IIf([CompletionDt]<=[DateRequired],1,0))+Sum(IIf([CompletionDt]>[DateRequired],1,0))+Sum(IIf([DateRequired]<Date(),1,0)) AS TotalCourseCt, AvoidError(IIf(Sum(IIf([CompletionDt]<=[DateRequired],1,0))+Sum(IIf([CompletionDt]>[DateRequired],1,0))+Sum(IIf([DateRequired]>Date(),1,0))+Sum(IIf([DateRequired]<Date(),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)))),0)AS [Total%Completed], 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 [%CompleteOnTime], 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 [%CompleteLate], IIf(Sum(IIf([DateRequired]<Date(),1,0))=0,0,Sum(IIf([DateRequired]<Date(),1,0))/(Sum(IIf([CompletionDt]<=[DateRequired],1,0))+Sum(IIf([CompletionDt]>[DateRequired],1,0))+Sum(IIf([DateRequired]<Date(),1,0)))) AS [%CompleteDel]
FROM qryEmpInfo LEFT JOIN qryEmpRequiredLearning ON qryEmpInfo.BEMS = qryEmpRequiredLearning.BEMS
WHERE (((qryEmpInfo.Mgr_OrgNo)=20))
GROUP BY qryEmpInfo.UnitChief, qryEmpInfo.Mgr_OrgNo, qryEmpInfo.BEMS, [LastName] & ", " & [FirstName];
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
This is what I have so far - but I need to get count of courses take by each employee (BEMS) that meet the various critieria ie. OnTime, Late, etc...

See ??????? portion of code.

K


Public Function CalculatePct()
Dim nOnTime As Long
Dim nDel As Long
Dim nLate As Long
Dim nTotalCourseCt As Long
Dim nCompleteOnTime As Long
Dim nCompleteDel As Long
Dim nComplete As Date
Dim nReqDate As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim n As Integer
Set db = CurrentDb()

strSQL = "SELECT qryEmpInfo.UnitChief, qryEmpInfo.Mgr_OrgNo, qryEmpInfo.BEMS," & _
            " [LastName] & ', ' & [FirstName] AS Employee, qryEmpRequiredLearning.CompletionDt, qryEmpRequiredLearning.DateRequired" & _
        " FROM qryEmpInfo LEFT JOIN qryEmpRequiredLearning ON qryEmpInfo.BEMS = qryEmpRequiredLearning.BEMS" & _
        " WHERE (((qryEmpInfo.Mgr_OrgNo) = 20))" & _
        " GROUP BY qryEmpInfo.UnitChief, qryEmpInfo.Mgr_OrgNo, qryEmpInfo.BEMS," & _
            " [LastName] & ', ' & [FirstName], qryEmpRequiredLearning.CompletionDt, qryEmpRequiredLearning.DateRequired" '
Set rs = db.OpenRecordset(strSQL)
    rs.MoveFirst
    Do Until rs.EOF
        nComplete = rs.Fields("CompletionDt")
        nReqDate = rs.Fields("DateRequired")
        gBems = rs.fields ("BEMS")
        
        For each BEMS.....???????????

        'OnTime Calculation
        If nComplete <= nReqDate Then
            
            nOnTime = Sum(IIf(nComplete <= nReqDate, 1, 0))
        End If
        If nComplete > nReqDate Then
            nLate = Sum(IIf(nComplete > nReqDate, 1, 0))
        End If
        
        rs.MoveNext
    Loop

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Karen,

  Let's step back for a minute.  Your function above is good, but it's heading in the wrong direction.  The function would simply be the formulas and/or descisions for each record in a query and be called for each row of the query rather then executing the query itself.  This will become clearer in a bit.  

  Here is what I would do to make this a lot simpler:  first start with a base query that gives you the basic data.  Don't worry about the %, grouping, etc.  So that query will look like this:

SELECT qryEmpInfo.UnitChief, qryEmpInfo.Mgr_OrgNo, qryEmpInfo.BEMS, [LastName] & ", " & [FirstName] AS Employee, IIf([CompletionDt]<=[DateRequired],1,0) AS OnTime, IIf([CompletionDt]>[DateRequired],1,0) AS Late, IIf([DateRequired]<Date(),1,0) AS Del FROM qryEmpInfo LEFT JOIN qryEmpRequiredLearning ON qryEmpInfo.BEMS = qryEmpRequiredLearning.BEMS

  Now save that query out.  Make sure it executes and returns data as you would expect and has all the data that you need.

  Now start a new query and use the saved query from above as a "table" in the design window.

  Now do your grouping, % calculations, etc.

  See if that simplifies things.  Generally with something complex, it's easier to build it up like this rather then trying to do it all in one query.

  As for the rest of the calculations, I'm still trying to figure out exactly what it is your going after.

JimD.
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
I created the first query per your suggestion, then the 2nd to calc the Total Count
Now I need to calculate percentages.

I always get this mixed up - which side goes where to get percentage

[totalCourseCt]/[OnTime] AS [Total%Completed]

or should it be the other way around, plus need to handle zero  - my curent way returns the #Error.

K
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  It would be:

   AvoidError(([OnTime] / ([OnTime] + [Late] + [Del]) * 100,0)

JimD.

   
 
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Total%Completed: AvoidError([OnTime]/([OnTime]+[Late]+[Del]),0)

Still returns #Error
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
I format the field as Percent
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  Sorry, that's not quite correct as you need to do it on the totals (your grouping).

  Save the group query out, then start a new query using that as a "table".

 You know have the name, etc and the formula should be:

 PercentComplete:AvoidError(([SumOfOnTime] / ([SumOfOnTime] + [SumOfLate] + [SumOfDel]) * 100,0)

  I would avoid using the wildcard character % in a field name, even if it is bracketed.

JimD.

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, this is the technique I use if I'm struggeling with a query.  

Often I find I can go back and consolidate some of them, but breaking things down step by step often lets me see how to get to the answer in the first place even though it may not be the most efficent overall.

JimD.
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
wouldn't this work just the same

 IIf([totalCourseCt]=0,0,([OnTime]/[totalCourseCt])) for %CompleteOnTime
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Save the group query out, then start a new query using that as a "table".

Do you mean I should create a table based on the first query instead of nesting the queryies?

K
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<wouldn't this work just the same>>

  No, because an IIF() evaluates both the true and false arguments before it desides which it needs.  Seems silly, but that's the way it works.   As a result, you'd still get a #Error.  And this is where a function would be useful.  Example:

Publich Function ReturnCompleteOnTime(varTotal as Variant, varOnTime as Variant) as double

    If IsNumeric(varTotal) = True and varTotal<>0 then
      ReturnCompleteOnTime = (varOnTime/varTotal)*100
    Else
      ReturnCompleteOnTime = 0
   End If

End Function

  The If check here is performed properly as you would expect.  You'd call it like this by defining a query column as:

PercentOnTime: ReturnCompleteOnTime([totalCourseCt],[OnTime])

JimD.

 
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Here is my final query result using nested queries -


SELECT qryEmpCalcSummary_TotalCt.UnitChief, qryEmpCalcSummary_TotalCt.Mgr_OrgNo, qryEmpCalcSummary_TotalCt.Employee,
qryEmpCalcSummary_TotalCt.OnTime,
qryEmpCalcSummary_TotalCt.Late,
qryEmpCalcSummary_TotalCt.Del,
qryEmpCalcSummary_TotalCt.TotalCourseCt,

IIf([totalCourseCt]=0,0,[OnTime]/([OnTime]+[Late]+[Del])) AS TotalPctCompleted,

IIf([totalCourseCt]=0,0,([OnTime]/[totalCourseCt])) AS CompleteOnTimePct,

IIf([totalCourseCt]=0,0,([Late]/[totalCourseCt])) AS CompleteLatePct,

IIf([totalCourseCt]=0,0,([Del]/[totalCourseCt])) AS CompleteDelPct

FROM qryEmpCalcSummary_TotalCt
GROUP BY qryEmpCalcSummary_TotalCt.UnitChief, qryEmpCalcSummary_TotalCt.Mgr_OrgNo, qryEmpCalcSummary_TotalCt.Employee, qryEmpCalcSummary_TotalCt.OnTime, qryEmpCalcSummary_TotalCt.Late, qryEmpCalcSummary_TotalCt.Del, qryEmpCalcSummary_TotalCt.TotalCourseCt;

It looks like a good base query to go forward with - do you seem any issues using this approach?

K
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Do you mean I should create a table based on the first query instead of nesting the queryies?>>

  No.  When you design a query, it can be based on tables as well as saved queries.  All I'm saying is to use the saved query.

JimD.
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
now I am a bit confuse how does your function calc the % complete for all OnTime, then Late, & Delinquent?

K
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
forget last post - however, is there a way to create a function to handle all three ONtime, late, & del percentages or should I create 3 separate funcitons?

Publich Function ReturnCompleteOnTime(varTotal as Variant, varOnTime as Variant) as double

    If IsNumeric(varTotal) = True and varTotal<>0 then
      ReturnCompleteOnTime = (varOnTime/varTotal)*100
    Else
      ReturnCompleteOnTime = 0
   End If

End Function
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 Can you get the base tables and the queries you've written into a small sample DB and post?

  I need to leave shortly and I don't want to leave you hanging with this over the weekend.

JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<forget last post - however, is there a way to create a function to handle all three ONtime, late, & del percentages or should I create 3 separate funcitons?>>

  I would just make it more generic:

Publich Function ReturnPercent(varTotal as Variant, varPortion as Variant) as double

    If IsNumeric(varTotal) = True and varTotal<>0 then
      ReturnPercent= (varPortion /varTotal)*100
    Else
      ReturnPercent = 0
   End If

End Function

PercentOnTime: ReturnPercent([totalCourseCt],[OnTime])
PercentLate: ReturnPercent([totalCourseCt],[Late])
PercentDel: ReturnPercent([totalCourseCt],[Del])

JimD.
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
That seems to do the trick except for Total Percent complete - since your equation only handles 2 variables at a time.

K
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Jeff that did the trick

THANKS for all you hard work, Have a great Weekend.

Karen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.