?
Solved

access query error overflow

Posted on 2014-09-24
8
Medium Priority
?
530 Views
Last Modified: 2014-10-15
I have attached a query giving the Overflow error when I attempt to run.  I have changed datatype to Double for the number fields, have checked all items are not Null or zero and when I remove the criteria <0.35, the query runs fine.  I want this query to show items less than 35%.
Access-overflow-error.docx
0
Comment
Question by:softsupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 40342772
Have you tried adding the calculated field to the underlying query?  For example, add to qryClaimComparisonYTD the column
([Free]+[Reduced])/[FRNtotal] AS FRPercentage
and then set the criteria in your current query to
WHERE FRPercentage < 0.35

OM Gang
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40342824
Your problem is obviously where FRNTotal is either NULL, zero, or extemely small.

You might want to consider something like:

SELECT qryClaimComparisonYTD.[Center Name]
, qryClaimComparisonYTD.ClaimDate
, qryClaimComparisonYTD.Free
, qryClaimComparisonYTD.Reduced
, qryClaimComparisonYTD.[Non-Needy]
, qryClaimComparisonYTD.FRNTotal
, IIF(NZ([FRNTOTAL], 0) < .0001, 100, ([Free]+[Reduced])/[FRNtotal]) AS FRPercentage
FROM qryClaimComparisonYTD
WHERE (((([Free]+[Reduced])/[FRNtotal])<0.35));

This would give you a percentage of 100 for those where [FRNTotal] is NULL or zero.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40343293
First, do not attach simple code as a document.
Then protect against Null values of FRNtotal:

SELECT
    qryClaimComparisonYTD.[Center Name],
    qryClaimComparisonYTD.ClaimDate,
    qryClaimComparisonYTD.Free,
    qryClaimComparisonYTD.Reduced,
    qryClaimComparisonYTD.[Non-Needy],
    qryClaimComparisonYTD.FRNTotal,
    IIf([FRNtotal] Is Null, 1, ([Free]+[Reduced])/Nz([FRNtotal],1)) AS FRPercentage
FROM
    qryClaimComparisonYTD
WHERE
    IIf([FRNtotal] Is Null, 1, ([Free]+[Reduced])/Nz([FRNtotal],1)) < 0.35;

/gustav
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:softsupport
ID: 40343919
Have tried all three suggestions above, however still receiving the overflow error.  
FYI....... If I remove the criteria <0.35, the query runs fine and shows all of  the FRPercentage.  Once the criteria is entered in the query... it overflows.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40343958
Check out the function provided in response to this similar question.Then you would modify your query like:

SELECT qryClaimComparisonYTD.[Center Name]
, qryClaimComparisonYTD.ClaimDate
, qryClaimComparisonYTD.Free
, qryClaimComparisonYTD.Reduced
, qryClaimComparisonYTD.[Non-Needy]
, qryClaimComparisonYTD.FRNTotal
, AvoidError(([Free]+[Reduced])/[FRNtotal], 2) AS FRPercentage
FROM qryClaimComparisonYTD

YOu might have to play with the 2nd AvoidError argument.
0
 

Author Comment

by:softsupport
ID: 40349959
The function provided has not determined the error for this query.  Perhaps I am not using properly.  Can you offer more assistance?
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40349985
Try this:

SELECT
     qryClaimComparisonYTD.[Center Name],
     qryClaimComparisonYTD.ClaimDate,
     qryClaimComparisonYTD.Free,
     qryClaimComparisonYTD.Reduced,
     qryClaimComparisonYTD.[Non-Needy],
     qryClaimComparisonYTD.FRNTotal,
     IIf([FRNtotal] Is Null, 1, ([Free]+[Reduced])/Nz([FRNtotal],1)) AS FRPercentage
 FROM
     qryClaimComparisonYTD
 WHERE
     CCur(IIf([FRNtotal] Is Null, 1, ([Free]+[Reduced])/Nz([FRNtotal],1))) < CCur(0.35);

 /gustav
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40350226
I'm surprised that the AvoidError() function is not identifying the problem.

I think you also need to test for [FRNTotal] = 0 and for the numerator (sum of two fields) also being NULL.  Try this, it will not actually compute the percentage, but should help you identify the record(s) causing the problem.

SELECT qryClaimComparisonYTD.[Center Name]
, qryClaimComparisonYTD.ClaimDate
, qryClaimComparisonYTD.Free
, qryClaimComparisonYTD.Reduced
, qryClaimComparisonYTD.[Non-Needy]
, qryClaimComparisonYTD.FRNTotal
, iif(IsNull([Free] + [Reduced]), True, False) as Numerator
, iif(IsNull([FRNTotal]), True, False) as DenomNull
, iif(NZ([FRNTotal], -1) = 0, True, False) as DenomZero
FROM qryClaimComparisonYTD
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

743 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