• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3050
  • Last Modified:

Overflow Error in MS Access SQL query

Experts:

I am getting an 'Overflow' error when running this expression in a query.

 Sum(([tblReview_Main]![NumberChecks]-([tblReview_Main]![NumberChecksIssuedwerrors]-[tblReview_Main]![Number_ABCErrors]-[tblReview_Main]![Procedural_Errors]))/[tblReview_Main]![NumberChecks])<---If I change this last data element to a Number Value it works fine. However, I get the error when I try to get the data from a data element.

-Sariff
0
Sariff
Asked:
Sariff
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
check for Null values or Zero
0
 
SariffAuthor Commented:
How?
0
 
Rey Obrero (Capricorn1)Commented:
try to incorporate the nz function in your expression

from help

Nz(variant, [valueifnull])
The Nz function has the following arguments.

Argument Description
variant A variable of data type Variant.
valueifnull Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string.
Note  If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values.
 
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rey Obrero (Capricorn1)Commented:
try this


Sum((Nz([tblReview_Main]![NumberChecks])-(nz([tblReview_Main]![NumberChecksIssuedwerrors])-nz([tblReview_Main]![Number_ABCErrors])-nz([tblReview_Main]![Procedural_Errors])))/nz([tblReview_Main]![NumberChecks]))
0
 
SariffAuthor Commented:
There were Zero values. I cleaned them up. Still get the 'Overflow' error.

-Sariff
0
 
mbizupCommented:
You need to decide what reults you want for the division if NumberChecks is 0.  This assumes you want the result of a divide-by-zero to be zero:

Sum(iif(tblreview_Main = 0, 0, ([tblReview_Main]![NumberChecks]-([tblReview_Main]![NumberChecksIssuedwerrors]-[tblReview_Main]![Number_ABCErrors]-[tblReview_Main]![Procedural_Errors]))/[tblReview_Main]![NumberChecks]))
0
 
mbizupCommented:
Correction:

Sum(iif(tblreview_Main!NumberChecks = 0, 0, ([tblReview_Main]![NumberChecks]-([tblReview_Main]![NumberChecksIssuedwerrors]-[tblReview_Main]![Number_ABCErrors]-[tblReview_Main]![Procedural_Errors]))/[tblReview_Main]![NumberChecks]))
0
 
Rey Obrero (Capricorn1)Commented:
better if you could zip the db with only that table and upload to www.ee-stuff.com
0
 
aesmikeCommented:
Put this function in one of your modules.  
'////////// code begins here /////////
Public Function DivZero(Numerator, Denominator)
    On Error Resume Next
    If Denominator = 0 Then
        DivZero = 0
    Else
        If IsNull(Numerator) Or IsNull(Denominator) Then
            DivZero = 0
        Else
            DivZero = Numerator / Denominator
        End If
    End If
End Function
'/////// Code Ends Here /////////////

Now Change your expression in the query to this:
Sum(DIVZERO(([tblReview_Main]![NumberChecks]-([tblReview_Main]![NumberChecksIssuedwerrors]-[tblReview_Main]![Number_ABCErrors]-[tblReview_Main]![Procedural_Errors])),[tblReview_Main]![NumberChecks]))
0
 
SariffAuthor Commented:
Sure. give me a few

-Sariff
0
 
mbizupCommented:
Sariff,
You followed up the accepted comment with
> There were Zero values. I cleaned them up. Still get the 'Overflow' error.

Can you explain your selection?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now