Solved

access query error overflow

Posted on 2014-09-24
8
453 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 (Access MVP)
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 50

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 (Access MVP)
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 50

Accepted Solution

by:
Gustav Brock earned 500 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 (Access MVP)
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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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