Union query throwing 'Invalid use of null' exception

Question

I have two queries in Access. Both of them are moderately nasty to create, but at the end of the process they do have the same number of fields with the same data types. They both work independently, producing the expected results.

Unfortunately,

SELECT * 
FROM [qry vaBaseQuery-S2]
UNION ALL SELECT *
FROM [qry BaseQuery];

throws two 'Invalid use of null' errors, one after the other. I've used union on Access 2000 queries with null values before without issue, so I'm a bit stumped. Can anyone suggest what might be happening here?

Further information that might be relevant:

• Neither query has any blank rows in it

• UNION SELECT * (without the ALL) throws the same error but only once?!

Edit:

• Using the field names instead of * doesn't help

Edit2:

• Given the query was going to be a make table query run from a form anyway, I just left it as two separate queries (one make table and one append) and trigger the two in sequence. Given the answers below, that sounds much less effort than trying to actually work out what Access is objecting too.

Answer 1

You most probably have some conditional (Iif()) data conversions (CStr() or alike) in your source queries. Access may optimize separate queries differently than in union; sometimes it evaluates conditional parts in very weird order.

Like in next oversimplified case:

Select Iif(int_fld is null, '0', CStr(int_fld)) As Something

This may throw "Invalid use of null" or not - depends on evaluation order.

Edit: forgot write correct expression, which doesn't give this error:

Select CStr(Iif(int_fld is null, 0, int_fld)) As Something

Answer 2

Arvo wrote: "sometimes [ACE/Jet] evaluates conditional parts in very weird order" -- I can vouch for that and not just when using UNION. Here's something I posted recently on SO where merely adding a WHERE clause to a query resulted in the engine evaluating in the wrong order causing an 'Invalid procedure call' error and I could not find a way round it.

SQL for parsing multi-line data?

I suggest you post the SQL code from the two Query objects. Perhaps someone can spot something the engine may have problems with.

Answer 3

As mentioned in edited question: Given the query was going to be a make table query run from a form anyway, I just left it as two separate queries (one make table and one append) and trigger the two in sequence.