Solved

#Error Handling

Posted on 2011-02-25
28
273 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Karen Schaefer
  • 17
  • 10
28 Comments
 
LVL 10

Expert Comment

by:VTKegan
ID: 34981271
You can just use the Nz( <<your expression >>, 0)
0
 

Author Comment

by:Karen Schaefer
ID: 34981291
Still get #Error - Maybe it is the Divide by Zero issue.
0
 

Author Comment

by:Karen Schaefer
ID: 34981316
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
 

Author Comment

by:Karen Schaefer
ID: 34981590
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
 
LVL 57
ID: 34981610

  #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
 

Author Comment

by:Karen Schaefer
ID: 34981698
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
 

Author Comment

by:Karen Schaefer
ID: 34981703
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
 

Author Comment

by:Karen Schaefer
ID: 34981840
0
 

Author Comment

by:Karen Schaefer
ID: 34982293
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
 
LVL 57
ID: 34982656
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
 

Author Comment

by:Karen Schaefer
ID: 34982801
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
 
LVL 57
ID: 34982863

  It would be:

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

JimD.

   
 
0
 

Author Comment

by:Karen Schaefer
ID: 34982885
Total%Completed: AvoidError([OnTime]/([OnTime]+[Late]+[Del]),0)

Still returns #Error
0
 

Author Comment

by:Karen Schaefer
ID: 34982890
I format the field as Percent
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 57
ID: 34982898

  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
 
LVL 57
ID: 34982911
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
 

Author Comment

by:Karen Schaefer
ID: 34982921
wouldn't this work just the same

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

Author Comment

by:Karen Schaefer
ID: 34982939
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
 
LVL 57
ID: 34982980
<<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
 

Author Comment

by:Karen Schaefer
ID: 34982985
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
 
LVL 57
ID: 34983000
<<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
 

Author Comment

by:Karen Schaefer
ID: 34983019
now I am a bit confuse how does your function calc the % complete for all OnTime, then Late, & Delinquent?

K
0
 

Author Comment

by:Karen Schaefer
ID: 34983033
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
 
LVL 57
ID: 34983038

 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
 
LVL 57
ID: 34983060
<<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
 

Author Comment

by:Karen Schaefer
ID: 34983110
That seems to do the trick except for Total Percent complete - since your equation only handles 2 variables at a time.

K
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 34983154
Karen,

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

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

JimD.
0
 

Author Closing Comment

by:Karen Schaefer
ID: 34983189
Jeff that did the trick

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

Karen
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now