Overflow!

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

I get an overflow message when Im tabulating averages from
Querys. Can anyone tell me what this message is and/or
what I can do about it.
 
What is the exact error message? Are you averaging in code
or in the query? If in code, have you considered using the
query to average (much faster)?
 
I treid to do the averaging in the Queries initailly. I
got the same message. I posted a question in the Queries
newsgroup but got no response. The exact error message I
got in both cases is Overflow! Thats all it says. I tried
to use the microsoft customer service, I typed in overflow
and there solution was to chang ACWZLIB.MDE to
ACWZLIB.OLD, and it did not work for me. I have been
running into many problems because of the size of my
database. Im not sure if this could also be related.
Thanks for your help, Im really stuck and Im having
trouble finding another way around this. - Stu
 
Stuart:

99% of the overflow errors occur because there is a divide by zero problem
in a query. If as an example you have a field which does something like :
Avg: Sum(FieldA)/Sum(FieldB), then change this to Avg:
IIF(Sum(NZ(FieldB,0)) >0 Then Sum(FieldA)/Sum(FieldB)

HTH
 
Im still getting the same message. Maybe I should tell
you about when I get the Overflow message.
1) I have a Query that is compsed of two other queries.
This Query has SystemTotal, CostPerSQFT and HourPerSQFT
for 23 different systems. The Query works fine until I
use the "Totals" in the fields. Even if i leave the
totals on group by, or average or sum, when i want to look
at the databse, the Overflow Messge appears.
2) In the report, I pulled a field from a Query (one of
the Queries that made up the Query in the last example).
As soon as i try to take the average of this field, and I
tried many different ways, Overflow appears.

Question: Im the databases behind my quries, fields that
do not have values are either blank or "0". Do they all
have to be the same? would that have any effect on this?
Thank you again for your help, I know this is a pain
because I have to explain it to you! _Stuart
 
Stuart:

If you are doing arithmatic calculations, natural null valuesare fine for
average etc, Access just ignores 'em. However, and this can be where you
run into your problem. If any of the fields that you are trying to do an
arithmatic calc on contains number values which are text in the output,
you'll have problems. (You can tell this because numbers get right
justified when viewed and text generally left justified (based on the
systems text reading settings left to right or right to left). If your
values are left justified in the underlying query you're trying to add to
the totals query, you'll get a problem. Numbers can get turned into text
in a query field if you use either the Format() function, NZ() function, or
if you use an IIF statement where if the you set a field's value to null
under a certain conditions; especially in union queries. Any of these will
render using the numeric functions of Jet (i.e. average, sum etc.) to error.
 
Back
Top