"Overflow" message in UNION query

[Mark Wickett]

Hi,

I have a UNION query that has started to generate an "Overflow" error
message when I execute it.

The SQL is "select * from query1 union select * from query2".

Both "query1" and "query2" execute fine when I try them separately - I only
get the "Overflow" in the union query. Both "query1" and "query2" have
identical fields (20 of them) in identical formats - the data is just sourced
from two distinct tables. Only "query1" has a calculation to add two numbers
together, so I can't see anything likely to cause a "divide by zero" error.

What is particularly puzzling is that this union query has been used
(unchanged) on a weekly basis for the last two years without error...!

Anyone have any ideas?

Thanks,
Mark

 

[John Spencer]

IF you are adding two integer numbers together and they exceed the
maximum value of an integer field (32K) then you will get an overflow error.

What are the field types involved?

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Mark Wickett]

Hi John,

Field types are both doubles - and actually, the nature of the source data
is that one field is always negative (or zero), so the addition could only
ever reduce the size of the result (it could possibly reduce it to a large
negative though...!) I have looked at these fields (source and result) to
confirm that these values are within acceptable ranges (-5000 to 2500000)

That aside, both sub-queries work independently without throwing this error
- so if it were a number blowout, I can't see how I wouldn't get this error
when I execute the sub-query?

Thanks,
Mark

 

[Mark Wickett]

On further investigation John was right... deeper into one of the queries,
there is a field that casts a number into an integer where the expected range
of the source data is 0-1,000. However, a single record had an [erroneous]
value of around 50,000 - which of course, blows an Integer. Despite the error
being in the source, I still should have trapped it - now corrected and happy.

The fact that Access was happy to display the query's result on its own, but
not when unioned with another, misled my investigations...

Thanks to John for making me doubt my original assumptions!

Regards,
Mark

Hi John,

Field types are both doubles - and actually, the nature of the source data
is that one field is always negative (or zero), so the addition could only
ever reduce the size of the result (it could possibly reduce it to a large
negative though...!) I have looked at these fields (source and result) to
confirm that these values are within acceptable ranges (-5000 to 2500000)

That aside, both sub-queries work independently without throwing this error
- so if it were a number blowout, I can't see how I wouldn't get this error
when I execute the sub-query?

Thanks,
Mark

 

[John]

I've had this overflow error happen twice for me

The first involved a custom function that returned line numbers. This
was for me to fix. I increased the variable in the function.

The second was a, if you excuse the French, a f'ing bitch to find.

In my case the overflow involved a val(mid(Field, First, Length))
statement. Now I had criteria to pull only the records I wanted. When
I checked the data in the table my math was perfect.

The problem I had was in section on another record that was filtered
out by the criteria. I had the value 256e400, The problem was the
criteria filtered this record out AFTER the record was returned by the
query. 256e400 is scientific notation. 256 * 10 to the power of 400.
which is a number bigger then access can handle.

I learned that *ALL* Records in the table are parsed in a query.
*THEN* the criteria filters out those records that don't meet the
criteria.

If during the "*ALL* Records are parsed" phase you have a number to
big for access to handle then you get "Overflow". My mid() returned
scientific notation that was way to big for access to handle.

I don't know if you have any criteria used in Query1, I don't know if
your using a Val() statement, and to make matters a bit worse, I don't
know the maximum value that a can be returned in a query.

But the scientific notation, overly large numbers, or a parsing
problem, is an issue that you can at least rule out.

John
MS Access Programmer