I have the following query - three separate queries joined with Union. Each query works independently, and the Union works properly with any two of the three, but when I try to use all three together, I get an error dialog saying my data types are incompatible. I have tried adding Union ALL, and removing the surrounding query (which exists only to sort the result set). When I do that, it initially shows a result set, but after a few seconds the same dialog pops up, and when I click Ok, the data disappears and is replaced by #NAME? in every field.
Code:
Select Rod, Druh, RodAutoID, DruhAutoID, cntZaz, cntLit FROM (
SELECT Rody.Druh AS Rod, Null AS Druh, Rody.DruhAutoID AS RodAutoID, Null AS DruhAutoID, Null AS cntZaz, ToD_LiteraturaPocet.cntLit, Rody.Druh AS Poradi
FROM TableOfDruhs AS Rody LEFT JOIN ToD_LiteraturaPocet ON Rody.DruhAutoID = ToD_LiteraturaPocet.DruhAutoID
Where (Rody.Druh Not Like '* *')
AND (Rody.UpTaxonAutoID Is Null)
AND (Rody.Druh Not Like '?')
AND (Rody.Druh Not Like '??')
UNION
SELECT Rody.Druh AS Rod, Druhy.Druh, Rody.DruhAutoID AS RodAutoID, Druhy.DruhAutoID, Null AS cntZaz, ToD_LiteraturaPocet.cntLit, Druhy.Druh AS Poradi
FROM (TableOfDruhs AS Druhy INNER JOIN TableOfDruhs AS Rody ON Druhy.UpTaxonAutoID = Rody.DruhAutoID) LEFT JOIN ToD_LiteraturaPocet ON Druhy.DruhAutoID = ToD_LiteraturaPocet.DruhAutoID
WHERE (Rody.Druh Not Like '* *')
And (Rody.Druh Not Like '?')
And (Rody.Druh Not Like '??')
AND (Rody.UpTaxonAutoID Is Null)
UNION
SELECT Null AS Rod, Druhy.Druh, Null AS RodAutoID, Druhy.DruhAutoID, Null AS cntZaz, ToD_LiteraturaPocet.cntLit, Druhy.Druh AS Poradi
FROM TableOfDruhs AS Druhy LEFT JOIN ToD_LiteraturaPocet ON Druhy.DruhAutoID = ToD_LiteraturaPocet.DruhAutoID
WHERE (Druhy.Druh Like '* *')
AND (Druhy.UpTaxonAutoID Is Null)
)
Order By Poradi