Overflow Error On Union QUery

[Paul]

Hi

I'm having a query problem I have not come across before.

I have a union query that incorporates 3 queries (each of which references
tables and queries and contain a number of calculations). All 3 queries run
fine.

The union query was working fine, but now there is more data in the system,
I get the following:

If I do a normal Union, I get the "This expression is typed incorrectly, or
is too complex..." message. If I change it to Union All, the data appears in
the data sheet for a second, then the message "Overflow" appears, then when
the message is cleared, all the values in the datasheet are replaced by
#Name?. I can scroll through the datasheet, but I get only 91 records when I
should be getting 390.

I have tried this in Access 97 and XP and get exactly the same result.

There is a further query that takes the results of the Union query and
filters/sums it. This query manages to return data, though I am not sure yet
whether it is all the data or just a portion of it.

I have tried simplifying the queries, but have gone about as far as I can
down that route. I did read somewhere that overflow can be related to data
types, but as I am doing a union and not doing any calculations on the data
in the 3underlying queries, that does not seem to be the problem.

Can anyone shed any light on this? If not I may have to start making temp
tables which will really bloat out the database if the users are not
instructed to do a regular compact.

Many thanks

Paul

 

[Andy Wilkinson]

I get a similar error with an attached XLS sheet which is
used in a UNION query.

If one of the date fields int the XLS is missing data I
get the "too complex" error When I check the file and
input the missing date all is OK.

This may not be what is causing your error but it may
just give you somewhere to look.

Andy
Perth Australia

-----Original Message-----
Hi

I'm having a query problem I have not come across before.

I have a union query that incorporates 3 queries (each of which references
tables and queries and contain a number of

calculations). All 3 queries run

 

[Paul]

Thanks Andy,

I have now found the problem!

One of the fields in the union was a calculated field with a division in it.
For my data set I had one record where the divisor was 0 and so was
returning an error - it seems that Access cannot union queries where the
results of one or more of those queries is #Error.