"Overflow" message in UNION query

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

--
Mark Wickett - Adelaide Bank - Australia

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 wrote:
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

--
Mark Wickett - Adelaide Bank - Australia

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 Spencer" wrote:

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 wrote:
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

--
Mark Wickett - Adelaide Bank - Australia

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

"Mark Wickett" wrote:

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 Spencer" wrote:

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 wrote:
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

--
Mark Wickett - Adelaide Bank - Australia