CODEWITHSUNDEEP
sqlms-accessunion
mavnn
mavnn

Reputation: 9459

Union query throwing 'Invalid use of null' exception

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:

Edit:

Edit2:

Upvotes: 0

Views: 3665

Answers (3)

mavnn
mavnn

Reputation: 9459

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.

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

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.

Upvotes: 1

Arvo
Arvo

Reputation: 10570

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

Upvotes: 1

Related Questions