Data type for nulls in UNION query

[Brian]

I have a union query that combines different types of account transactions
(payments, invoices, and adjustments) for an account activity report.

SELECT LookupAdjustment.*
FROM LookupAdjustment
UNION ALL
SELECT LookupInvoice.*
FROM LookupInvoice
UNION ALL SELECT LookupPayment.*
FROM LookupPayment;

I have an InvoiceID field in each of the three source queries. It should be
null for the adjustment and payment-related queries; however, when I enter it
as Invoice: Null in those two, it results in "Data type mismatch in criteria
expression) when running the UNION query. If I use Invoice:0 instead, it
works, but then I have to convert the zeros or empty strings back to nulls in
the report that uses the union query.

Strangely, placing the invoice-related query as the first one in the UNION
query allows me to use the nulls in the other two. Evidently, the UNION
cannot handle a number in that field once it has inferred it at runtime to be
a variant(?), but it can handle a null once it has inferred it to be a Long.

 

[Ken Snell \(MVP\)]

If you have a field that will be set to NULL deliberarly in a union query,
then that subquery cannot be the first one in the UNION query. This is what
you're observing. So just rearrange your query to conform to this ACCESS /
Jet behavior.

 

[Brian]

Thank you. At least now I can quit looking for a more sophisticated answer
that does not exist.

 

[Brian]

Given that one of the purposes of a UNION query can be to combine similar but
not exactly-matched data sets, it just occurrred to me that I will likely
have a little more complex situation soon: where one subquery has one null
field and another subquery has a different null field. I guess in that case I
would just have to revert to my original plan of populating one of them with
a dummy value that can be converted back to null in the report based upon the
query.

 

[Ken Snell \(MVP\)]

There is no problem for a UNION query if a field in a record in one of the
subqueries contains a NULL value, so long as that field is not NULL in all
records from that subquery. Jet scans the records from each subquery to
determine the data type for the unioned field, so one (or more) NULL values
are ok because other records in that subquery will have a non-NULL value and
thus an obvious data type.
--

Ken Snell
<MS ACCESS MVP>

 

[Allen Browne]

Use a IIf() expression, e.g.:
IIf(False, 0, Null)

False will never be true, so the zero will never be assigned, but the
presence of the alternative is enough to give JET a hint of the intended
data typel.

 

[Brian]

Thank you, Allen.

Yes, that was just enough information to allow Jet to infer the intended
data type.