RE: Query OVERFLOW Error
- From: Jerry Whittle <JerryWhittle@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 22 Feb 2007 11:33:55 -0800
While I haven't seen this in Access, some other databases are very picky
about the order of the WHERE clause. They often start at the bottom and work
their way up. In your case that could cause a divide by zero error. Try
moving the last line up to the top of the WHERE clause.
To make matters worse, Access might be optimizing the SQL statement and
putting that part of the WHERE clause where ever it wants like moving it to
the bottom again! This happened to me when moving from Oracle 8i to 10g. In
cases like that you have to write a subquery to gather up all the records
where not null or >0 then act on that record set. You could try saving the
query that works then creating a second query based on it to get the rest of
the information.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"John D" wrote:
I've tried as best I can to follow suggestions in this forum about "fixing".
the Overflow error I'm getting in my query, but so far no good.
I have a table of financial statement values - 1 record per value. Three of
the fields in the table are [OrgID], [Activity Code], and [Value]. [Value] is
Numeric - Single data type.
[OrgID] identifies nonprofit organizations. [Activity] is a code, 1 through
54, that defines different services that can be provided by those nonprofit
organizations. A nonprofit can provide any number of services, but there is
only 1 [Value] associated with each [OrgID] - [Activity] combination.
I based a Crosstab Query [CT_RevAll] on this table where [OrgID] are the
rows, [Activity] are the columns, and [Value] are the "cells". I have a
selection criteria that limits [Value] to Total Revenue gained from each
service.
I'm building a Select Query that is attempting to select all [OrgID]'s from
the Crosstab Query where more than two-thirds of that nonprofit's revenue is
derived from Activity 54. When I run that query I get the "Overflow" message.
After several experiments, this is the "simple" expression of the Query's SQL:
SELECT CT_RevAll.OrgID,
CT_RevAll.[Value],
CT_RevAll.[54],
Nz([CT_RevAll]![54],0)/Nz([CT_RevAll]![Value],0) AS [Water %]
FROM CT_RevAll
WHERE
(
((CT_RevAll.[Value]) Is Not Null And (CT_RevAll.[Value])>0)
AND
((CT_RevAll.[54]) Is Not Null And (CT_RevAll.[54])>0))
AND
((Nz([CT_RevAll]![54],0)/Nz([CT_RevAll]![Value],0))>0.666)
If I take the last "AND" clause out, the query runs. With it in, I get
"Overflow".
I realize I probably don't need the Nz in the calculation expression in the
SELECT clause because I'm converting both numerator and denominator Non Null
values to Zero in the WHERE clause. Also, I'm selecting only numerator and
denominator values that are >0, which prevents the division by 0 problem (I
think).
Anyone see what's the problem? Thanks - John D
- Prev by Date: Re: Query won't open in design view
- Next by Date: Re: Adding records
- Previous by thread: Re: Checking against input mask
- Next by thread: Re: Query OVERFLOW Error
- Index(es):
Relevant Pages
|
