MS Access SQL - UNION Returns Empty Set

Question

I ran into a strange behaviour within Access. I basically have two predefined queries within my VBA-Project that are needed in different parts of the programm which are working fine.

Now I need a query which returns both result-sets as one. So my go to Solution was to create a new query combining the other two queries. Just like:

SELECT * FROM query1
UNION
SELECT * FROM query2;

Both queries have the same layout in their result. But if the result of query1 is empty and query2 has some records in its result I receive an empty result from the code above. The other way (empty result for query2 - several results for query1) it is delivering me a complete result.

I also tested the above code with the two queries switched. But the behaviour was the same. Same thing with UNION ALL.

What could be the issue here?

Edit: So, I did some further testing. The reason seems to be within query2. It can't even be unionized with itself. So if i try:

SELECT * FROM query2
UNION ALL
SELECT * FROM query2;

I again get an empty set. Same when i just use one field in the SELECT-part.

Edit2: OK it gets better. The Dataset is empty but within the table-view of the query result i have all the data within the filters of the columns. But if I do a Count(rowname) I get a 0 in return.

Sadly that doesn't solve the Problem. But thanks for trying. ;) — TRA
To be clear, when both queries have at least one record, or if query2 has at least one record, everything works as expected? It is only when query1 returns no records that the issue occurs? Can you provide the SQL for these queries? Have you tried replacing query1 and query2 with their full SQL code? That may give you a meaningful error. — SunKnight0
No, the UNION always failed even if there were results within query 2. After two days of testing I was fed up and moved the code to another machine. Worked there. So after exporting it on the new machine and transferring it to my PC everything was functioning as it should. So I guess it was a problem related to the frontend I was using. :/ But still a big "Thank you!" for your input. ;) — TRA

TRA TRA · Accepted Answer · 2017-07-29T22:33:36

After two days of testing I was fed up and moved the code to another machine. Worked there. So after exporting it on the new machine and transferring it to my PC everything was functioning as it should. So I guess it was a problem related to the frontend I was using.

So if you run into smth. similar make sure that your project isn't corrupted in any way. (even if there aren't any errors shown by Access)

MS Access SQL - UNION Returns Empty Set

1 Answers