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.

 

[MrAsker]

I know this kind of an old thread but I think I have the same problem but in Access 2010 and I'm not sure how I can or should solve it.

I do basically the same, get two ResultSets that I want use UNION or maybe UNION ALL on. But I keep getting the error message "Data type mismatch in criteria expression".

If run each query separately they work, but not when I put a UNION or UNION ALL between them.

I've tried to make a dummy row to get the def right by using this IIf for each field, but either I am doing it really wrong or it is not helping. See the attached file "w_def" for what I've tried.

As you might see I'm not an expert, I'm just started working with Access so maybe this is something really easy.

Either way, I really appreciate any help I can get

Thank you, Allen.

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

"Allen Browne" wrote:

> 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.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Brian" <[email protected]> wrote in message
>