Overflow error?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I added a few new calculation fields to an existing query and now when I run
it, I get an Overflow error.
What is an overflow error? Which direction do I go to fix it?
Thanks
 
Often it means that you're trying to put a value into a field that isn't
"big" enough to accept it.

Integer type can hold values from -32,768 to 32,767

Long (long integer) type can hold values from -2,147,483,648 to
2,147,483,647

Single (single-precision floating-point) can hold values from -3.402823E38
to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for
positive values

Double (double-precision floating-point) can hold values
from -1.79769313486231E308 to -4.94065645841247E-324 for negative values;
4.94065645841247E-324 to 1.79769313486232E308 for positive values

Currency (scaled integer) can hold values from -922,337,203,685,477.5808 to
922,337,203,685,477.5807
 
An overflow error most generally occurs when a value is too large to fit into
the type that receiving field is of. For instance, a calculation that
results in a value of 1,000,000 would give an overflow error if it was put
into a field of type Integer. Without knowing exactly what the calculations
are, I can't provide more assistance, but I would start looking at the
resultant values and what data type the calculations create.

HTH
 
I am calculating percentages, the formula I'm using is:
(1-([xxlate loads]/[xxtotal loads])) AS [xxpercent of late loads]

Could it be a "0" zero value or a null value that causes this calc to return
an overflow error?
 
Could it be a "0" zero value or a null value that causes this calc to
return
an overflow error?
Click to expand...

Yes, definitely. I have had Overflow errors that were really division by
zero errors. (Or maybe they were Division By Null errors, now that I think
about it).

--
George Nicholson

Remove 'Junk' from return address.


PhilGTI said:
I am calculating percentages, the formula I'm using is:
(1-([xxlate loads]/[xxtotal loads])) AS [xxpercent of late loads]

Could it be a "0" zero value or a null value that causes this calc to
return
an overflow error?

darrep said:
An overflow error most generally occurs when a value is too large to fit
into
the type that receiving field is of. For instance, a calculation that
results in a value of 1,000,000 would give an overflow error if it was
put
into a field of type Integer. Without knowing exactly what the
calculations
are, I can't provide more assistance, but I would start looking at the
resultant values and what data type the calculations create.

HTH
Click to expand...
Click to expand...
 
George Nicholson said:
zero errors. (Or maybe they were Division By Null errors, now that I think
about it).
Click to expand...
Ok, now that I know the overflow errors are caused from division by null,
what's the best way to change the nulls to "0". I'm using Access 97, so if
I remember correctly Nz doesn't work. Atleast it didn't a few minutes ago
when I tried it.
Here is the formula causing the overflow:
(1-([xxlate loads]/[xxtotal loads])) AS [xxpercent of late loads]
 
It's been a while since I've used Access 97. I don't remember that NZ wasn't
built-in. Maybe I wrote my own?

In any case, NZ is just a convenient way to check for Null & provide an
alternate when encountered. You can use an Iif() to do the same thing:

Iif(IsNull([xxtotal loads]),0,(1-([xxlate loads]/[xxtotal loads]))) AS
[xxpercent of late loads]

(This assumes that division by zero is not possible with your existing
data.)
--
George Nicholson

Remove 'Junk' from return address.

PhilGTI said:
George Nicholson said:
zero errors. (Or maybe they were Division By Null errors, now that I
think
about it).
Click to expand...
Ok, now that I know the overflow errors are caused from division by null,
what's the best way to change the nulls to "0". I'm using Access 97, so
if
I remember correctly Nz doesn't work. Atleast it didn't a few minutes ago
when I tried it.
Here is the formula causing the overflow:
(1-([xxlate loads]/[xxtotal loads])) AS [xxpercent of late loads]
Click to expand...
 
Actually division by NULL won't cause any error other than returning a Null
value. Division by ZERO will cause an error.

You need to test to see if you would be dividing by zero and in that case assign
some value. Null if you want it blank or -999 if you want an arbitrary value to
show up, or Zero if you want it to be zero.

IIF([xxtotal loads]= 0, Null ,(1-([xxlate loads]/[xxtotal loads]))) AS
[xxpercent of late loads]
George Nicholson said:
zero errors. (Or maybe they were Division By Null errors, now that I think
about it).
Click to expand...
Ok, now that I know the overflow errors are caused from division by null,
what's the best way to change the nulls to "0". I'm using Access 97, so if
I remember correctly Nz doesn't work. Atleast it didn't a few minutes ago
when I tried it.
Here is the formula causing the overflow:
(1-([xxlate loads]/[xxtotal loads])) AS [xxpercent of late loads]
Click to expand...
 
I am getting an overflow error when I try to export a report from Access 2003
to an excel file; any thoughts?

John Spencer (MVP) said:
Actually division by NULL won't cause any error other than returning a Null
value. Division by ZERO will cause an error.

You need to test to see if you would be dividing by zero and in that case assign
some value. Null if you want it blank or -999 if you want an arbitrary value to
show up, or Zero if you want it to be zero.

IIF([xxtotal loads]= 0, Null ,(1-([xxlate loads]/[xxtotal loads]))) AS
[xxpercent of late loads]
George Nicholson said:
Yes, definitely. I have had Overflow errors that were really division by
zero errors. (Or maybe they were Division By Null errors, now that I think
about it).
Click to expand...
Ok, now that I know the overflow errors are caused from division by null,
what's the best way to change the nulls to "0". I'm using Access 97, so if
I remember correctly Nz doesn't work. Atleast it didn't a few minutes ago
when I tried it.
Here is the formula causing the overflow:
(1-([xxlate loads]/[xxtotal loads])) AS [xxpercent of late loads]
Click to expand...
Click to expand...
 
First thought - try starting a new thread instead of tacking onto a old one that
is only peripherally related.

Second thought - I have no idea why you are having the problem. But I do know
that I would avoid exporting a report to Excel; I would export the report's
source (query or table) instead.
I am getting an overflow error when I try to export a report from Access 2003
to an excel file; any thoughts?

John Spencer (MVP) said:
Actually division by NULL won't cause any error other than returning a Null
value. Division by ZERO will cause an error.

You need to test to see if you would be dividing by zero and in that case assign
some value. Null if you want it blank or -999 if you want an arbitrary value to
show up, or Zero if you want it to be zero.

IIF([xxtotal loads]= 0, Null ,(1-([xxlate loads]/[xxtotal loads]))) AS
[xxpercent of late loads]
:

Yes, definitely. I have had Overflow errors that were really division by
zero errors. (Or maybe they were Division By Null errors, now that I think
about it).

Ok, now that I know the overflow errors are caused from division by null,
what's the best way to change the nulls to "0". I'm using Access 97, so if
I remember correctly Nz doesn't work. Atleast it didn't a few minutes ago
when I tried it.
Here is the formula causing the overflow:
(1-([xxlate loads]/[xxtotal loads])) AS [xxpercent of late loads]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top