Solved

access query error overflow

Posted on 2014-09-24
8
294 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 28

Expert Comment

by:omgang
Comment Utility
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)
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 

Author Comment

by:softsupport
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
The function provided has not determined the error for this query.  Perhaps I am not using properly.  Can you offer more assistance?
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
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)
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

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…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

728 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

9 Experts available now in Live!

Get 1:1 Help Now