# #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
###### Who is Participating?

President / OwnerCommented:
Karen,

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

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

JimD.
0

Commented:
You can just use the Nz( <<your expression >>, 0)
0

BI ANALYSTAuthor Commented:
Still get #Error - Maybe it is the Divide by Zero issue.
0

BI 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

BI 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

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
``````
0

BI 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

BI 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

BI ANALYSTAuthor Commented:
0

BI 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
``````
0

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

BI 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

President / OwnerCommented:

It would be:

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

JimD.

0

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

Still returns #Error
0

BI ANALYSTAuthor Commented:
I format the field as Percent
0

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

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

BI ANALYSTAuthor Commented:
wouldn't this work just the same

IIf([totalCourseCt]=0,0,([OnTime]/[totalCourseCt])) for %CompleteOnTime
0

BI 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

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

BI 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

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

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

K
0

BI 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

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

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

BI 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

BI 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.