Sariff
asked on
Overflow Error in MS Access SQL query
Experts:
I am getting an 'Overflow' error when running this expression in a query.
Sum(([tblReview_Main]![Num
-Sariff
I am getting an 'Overflow' error when running this expression in a query.
Sum(([tblReview_Main]
Rey Obrero (Capricorn1)
check for Null values or Zero
ASKER
How?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
There were Zero values. I cleaned them up. Still get the 'Overflow' error.
-Sariff
-Sariff
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]![NumberC
Sum(iif(tblreview_Main = 0, 0, ([tblReview_Main]![NumberC
Correction:
Sum(iif(tblreview_Main!Num
Sum(iif(tblreview_Main!Num
better if you could zip the db with only that table and upload to www.ee-stuff.com
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_Ma
'////////// 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_Ma
ASKER
Sure. give me a few
-Sariff
-Sariff
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?
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?