HELP!!! with Access 2003 "overflow" dialog box.

  • Thread starter Thread starter Todd A. Anderson
  • Start date Start date
T

Todd A. Anderson

When I run the following query, everything is ok.

SELECT CN, count(*)*4
FROM CFUT
GROUP BY CFUT.CN;

However, when I add a WHERE clause to the statement that checks if the
MYFLOAT column is less than 0.05 and I try to run the query then access
pops up a dialog box whose title box says "Microsoft Office Access" and
whose
contents simply says "Overflow" with an OK button.

SELECT CN, count(*)*4
FROM CFUT
WHERE CFUT.MYFLOAT < 0.05
GROUP BY CFUT.CN;

Note, if I change the WHERE clause to look at an integer column then I do
not
get this problem so I suspect it may be floating point related.

Please help!!

Todd
 
Todd A. Anderson said:
When I run the following query, everything is ok.

SELECT CN, count(*)*4
FROM CFUT
GROUP BY CFUT.CN;

However, when I add a WHERE clause to the statement that checks if the
MYFLOAT column is less than 0.05 and I try to run the query then
access pops up a dialog box whose title box says "Microsoft Office
Access" and whose
contents simply says "Overflow" with an OK button.

SELECT CN, count(*)*4
FROM CFUT
WHERE CFUT.MYFLOAT < 0.05
GROUP BY CFUT.CN;

Note, if I change the WHERE clause to look at an integer column then
I do not
get this problem so I suspect it may be floating point related.

Please help!!

Todd
Click to expand...

I'm using Access 2002, so I can't test with 2003, but a quick table I
threw together to support this query doesn't display this problem. Is
CFUT a table or another query? If a table, please post the full design
of the table; if a query, please post the SQL of the query and its base
tables. Does it matter what data is in CFUT? Do you get the same
result if CFUT is empty? Are you running Jet 4 SP7 or SP8?
 
Dirk Goldgar said:
I'm using Access 2002, so I can't test with 2003, but a quick table I
threw together to support this query doesn't display this problem. Is
CFUT a table or another query? If a table, please post the full design
of the table; if a query, please post the SQL of the query and its base
tables. Does it matter what data is in CFUT? Do you get the same
result if CFUT is empty? Are you running Jet 4 SP7 or SP8?
Click to expand...

Actually, the simplied query SELECT * FROM CFUT WHERE CFUT.MYFLOAT < 0.05;
also generates the overflow error. I am using Jet 4 SP8 but the same error
appeared in Jet 4 SP7 (I upgraded Jet in an attempt to fix this problem).
CFUT
is another query who sources are other queries whose sources are other
queries.
My database has 5 or 6 base tables and around 8 or 9 stored queries at the
moment
and I would have to sanitize the table, fields, query names before I could
post everything.
If that is necessary then I can do it.

I just realized what the problem might be. Some of the rows have a NULL
value for
the percentage column. I can get rid of this if you can tell me how to have
a default
value for a column generated by a stored query.
 
Todd A. Anderson said:
Actually, the simplied query SELECT * FROM CFUT WHERE CFUT.MYFLOAT <
0.05; also generates the overflow error. I am using Jet 4 SP8 but
the same error appeared in Jet 4 SP7 (I upgraded Jet in an attempt to
fix this problem). CFUT
is another query who sources are other queries whose sources are other
queries.
My database has 5 or 6 base tables and around 8 or 9 stored queries
at the moment
and I would have to sanitize the table, fields, query names before I
could post everything.
If that is necessary then I can do it.

I just realized what the problem might be. Some of the rows have a
NULL value for
the percentage column. I can get rid of this if you can tell me how
to have a default
value for a column generated by a stored query.
Click to expand...

I don't think having a NULL value for MYFLOAT can be the issue, though
of course you could wrap it in the Nz() function to make sure. But if
MYFLOAT (which I take to be the "percentage" column you refer) is a
calculated field, or is derived from a calculated field, my guess is
that, for some records in the base tables, performing the calculation
results in an arithmetic overflow. This may not cause an error when you
don't use this field as a criterion, because the query engine will put
off evaluating the calculation until the last possible moment, and maybe
the records that would have resulted in an overflow have already been
excluded. However, when you make this calculated field a criterion,
possibly the query engine relocates the evaluation to some earlier stage
of the query's processing, as it attempts to apply all restrictive
criteria as early in the query process as it can (thus limiting the
number of records to be handled later on).

That's only a guess, but if I were you, and if this field *is* the
result of a calculation, I'd certainly look at the nature of that
calculation and its inputs, see if there are any possible argument
values that could result in an overflow, and take steps to guard against
them. The IIf() function may be useful in such an endeavor. Please let
me know if (a) my guess is wrong, (b) my guess is right and you fix the
problem, or (c) my guess is right but you need more help. If (c),
please post the expression(s) in your queries that go into calculating
MYFLOAT.
 
Back
Top