"Division by zero", but...

[Guest]

I have a UNION query that's merging the results of several individual
queries. When I run the UNION, i briefly see the results of the query as
expected. A few seconds later, i get a "Division by zero" error, and all
fields go to #Name? errors.

But, when i run the individual queries that are used in the UNION, i don't
get *any* "Division by zero" errors. What's going on???

rachael

 

[Allen Browne]

One possibility is that Access is struggling to identify the data type.

JET uses the results from the first SELECT to identify the data type for the
column. If the first SELECT returned Null or integer values in all records,
that would determine the data type of the column (which could end up as
binary or an integer type.) The subsequent SELECTs could then be rounded,
and after rounding you could get a division by zero that did not occur in
any of the individual SELECTs.

If that is the cause, you might be able to solve it by re-ordering the
SELECTs, or by typecasting the calcuation. You can even use an expression
like this:
IIf(False, 0.5, [Field1] / [Field2])
Of course, False is never true, so it never returns the 0.5, but it's enough
to give JET a clue as to the data type to use if Field1 / Field2 returns
Null in all records.

(Of couse, you also want handle the case where Field2 is zero.)

If the query calls a VBA function, there are other possible causes of the
error.

 

[DAVID]

I think that you only get that behaviour when
one invalid row invalidates all rows.

Are you using a UNION query or a UNION ALL query?

Try with UNION ALL to see if you can identify
the row(s) which cause the query to fail.

(david)