none
Query finds non-zero value where column shows contents zero RRS feed

  • Question

  • Here is the query in which you can see column 'BalDue'. I have two records being returned by a SELECT on this query (with a WHERE clause of 'BalDue<0.0') where in the query's datasheet I see the value $0.00. Is this some type of rounding issue?

    SELECT C.CaseNumber, C.CaseStatus, CT.CaseType, IIF(IsProvider(S.ID),S.LastName, IIF(NZ(S.Firstname=""),S.Lastname,S.LastName & ", " & S.FirstName)) AS Investigatee, C.Investigator, S.LastName, ((IIF(LEFT(CT.CaseType,6)='MEMBER',C.Recoveries + C.InvestigativeCosts + C.HealthPlanSavings,C.Recoveries + C.InvestigativeCosts)) - NZ(P.TotalPaid,0)) AS BalDue, C.CmpPlus30, C.CmpPlus60, C.Target, C.ProsecutionCaseNumber, C.SourceCaseNumber, C.TaskforceCase, C.SubjectId
    FROM (([Beth Afirms] AS C LEFT JOIN tblSubjects AS S ON C.SubjectId = S.ID) LEFT JOIN qryCasePayments AS P ON C.CaseNumber = P.CaseNumber) LEFT JOIN tblCaseTypes AS CT ON C.CaseTypeID = CT.ID
    ORDER BY C.CaseNumber DESC;

    Thursday, February 25, 2016 9:20 PM

Answers

  • CCur() did not work, not sure why, the answer was to use the Round(...,2) function, my working SQL is now:

    SELECT C.CaseNumber, C.CaseStatus, CT.CaseType, IIF(IsProvider(S.ID),S.LastName, IIF(NZ(S.Firstname=""),S.Lastname,S.LastName & ", " & S.FirstName)) AS Investigatee, C.Investigator, S.LastName, Round(((IIF(LEFT(CT.CaseType,6)='MEMBER',C.Recoveries + C.InvestigativeCosts + C.HealthPlanSavings,C.Recoveries + C.InvestigativeCosts)) - NZ(P.TotalPaid,0)),2) AS BalDue, C.CmpPlus30, C.CmpPlus60, C.Target, C.ProsecutionCaseNumber, C.SourceCaseNumber, C.TaskforceCase, C.SubjectId
    FROM (([Beth Afirms] AS C LEFT JOIN tblSubjects AS S ON C.SubjectId = S.ID) LEFT JOIN qryCasePayments AS P ON C.CaseNumber = P.CaseNumber) LEFT JOIN tblCaseTypes AS CT ON C.CaseTypeID = CT.ID ORDER BY C.CaseNumber DESC;




    Tuesday, March 1, 2016 12:47 AM

All replies

  • Here is the query in which you can see column 'BalDue'. I have two records being returned by a SELECT on this query (with a WHERE clause of 'BalDue<0.0') where in the query's datasheet I see the value $0.00. Is this some type of rounding issue?

    Hi ATGNWT,

    I think you are right with the assumption of a rounding issue.

    You can try to solve this by converting the result of the calculation to Currency format (this rounds to 4 decimals), or - when the result is in cents - round it to 2 decimals:

      CCur(C.Recoveries + C.InvestigativeCosts + C.HealthPlanSavings,C.Recoveries + C.InvestigativeCosts)) - NZ(P.TotalPaid,0))) AS BalDue,

    Imb.

     

    Thursday, February 25, 2016 9:36 PM
  • It seems to me your  Parenthesis are not balanced in the correct places.

    ((        IIF(   LEFT (CT.CaseType,6)= 'MEMBER',   C.Recoveries + C.InvestigativeCosts + C.HealthPlanSavings,    C.Recoveries + C.InvestigativeCosts)) - NZ(P.TotalPaid,0)     ) AS BalDue,


    Build a little, test a little


    Thursday, February 25, 2016 10:45 PM
  • In addition to Ken's response I'd like to say that WHERE clause of 'BalDue<0.0' makes no sense mathematically. Zero is zero. If you wanted to find a number less than 1 THEN you would use the 0.0... as in 0.01 because it indicates a number less than 1, not a decimal of zero.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Thursday, February 25, 2016 10:56 PM
  • In addition to Ken's response I'd like to say that WHERE clause of 'BalDue<0.0' makes no sense mathematically. Zero is zero.
      Criteria of <0  will return negative values that exist.

    Build a little, test a little

    Thursday, February 25, 2016 11:11 PM
  • In addition to Ken's response I'd like to say that WHERE clause of 'BalDue<0.0' makes no sense mathematically. Zero is zero. If you wanted to find a number less than 1 THEN you would use the 0.0... as in 0.01 because it indicates a number less than 1, not a decimal of zero.

    Hi Bill,

    Because of the natural inaccuracy of reals you can have a value of -0.000000000001, that is displayed as 0, but in reality it is indeed less than 0.

    Appropriate rounding is important in these situations.

    Imb.

    Thursday, February 25, 2016 11:16 PM
  • Karl & Imb - Thanks for the clarification.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Friday, February 26, 2016 5:15 PM
  • I've dissected it and can see nothing wrong with the parentheses other than I may have more than is necessary.
    The IIF is evaluated and then the TotalPaid value is subtracted from the result.

    Incidentally, all of the columns being operated on here are defined as Currency. But I'm thinking there are intermediate results being converted to some other type. The resulting query datasheet indicates the two records have a value of $0.00, however they are being caught by my 'WHERE < 0' clause.

    Tuesday, March 1, 2016 12:39 AM
  • CCur() did not work, not sure why, the answer was to use the Round(...,2) function, my working SQL is now:

    SELECT C.CaseNumber, C.CaseStatus, CT.CaseType, IIF(IsProvider(S.ID),S.LastName, IIF(NZ(S.Firstname=""),S.Lastname,S.LastName & ", " & S.FirstName)) AS Investigatee, C.Investigator, S.LastName, Round(((IIF(LEFT(CT.CaseType,6)='MEMBER',C.Recoveries + C.InvestigativeCosts + C.HealthPlanSavings,C.Recoveries + C.InvestigativeCosts)) - NZ(P.TotalPaid,0)),2) AS BalDue, C.CmpPlus30, C.CmpPlus60, C.Target, C.ProsecutionCaseNumber, C.SourceCaseNumber, C.TaskforceCase, C.SubjectId
    FROM (([Beth Afirms] AS C LEFT JOIN tblSubjects AS S ON C.SubjectId = S.ID) LEFT JOIN qryCasePayments AS P ON C.CaseNumber = P.CaseNumber) LEFT JOIN tblCaseTypes AS CT ON C.CaseTypeID = CT.ID ORDER BY C.CaseNumber DESC;




    Tuesday, March 1, 2016 12:47 AM