Solved

access query error overflow

Posted on 2014-09-24
8
419 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 47

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
Independent Software Vendors: 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 47

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 47

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

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