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.

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.
--

Ken Snell
<MS ACCESS MVP>



"Brian" <(E-Mail Removed)> wrote in message
news:4C8EEDDD-9679-4ECB-9456-(E-Mail Removed)...
>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.
>

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

"Ken Snell (MVP)" wrote:

> 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.
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
>
> "Brian" <(E-Mail Removed)> wrote in message
> news:4C8EEDDD-9679-4ECB-9456-(E-Mail Removed)...
> >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.
> >
>
>
>

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

> 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.
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
>
> "Brian" <(E-Mail Removed)> wrote in message
> news:4C8EEDDD-9679-4ECB-9456-(E-Mail Removed)...
> >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.
> >
>
>
>

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>




"Brian" <(E-Mail Removed)> wrote in message
news:5D92819F-EF3A-47F2-A0AC-(E-Mail Removed)...
> 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)" wrote:
>
>> 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.
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>>
>> "Brian" <(E-Mail Removed)> wrote in message
>> news:4C8EEDDD-9679-4ECB-9456-(E-Mail Removed)...
>> >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.
>> >
>>
>>
>>

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 - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brian" <(E-Mail Removed)> wrote in message
news:4C8EEDDD-9679-4ECB-9456-(E-Mail Removed)...
>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.
>

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 - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Brian" <(E-Mail Removed)> wrote in message
> news:4C8EEDDD-9679-4ECB-9456-(E-Mail Removed)...
> >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.
> >
>
>