Data mismatch error in multiple UNION SQL Jet query

Question

I have the following query in Jet SQL. Basically each of the subqueries A,B and D generate one column in the final table. If I run the query as is I get a 'Data type mismatch in criteria expression'. If I remove any one of A, B, D it works fine. I have tried changing the order of A/B/D with no effect.

If I take each of A/B/D in their own right they execute fine.

Thanks Andy

SELECT A.accCategory, A.ID, A.Account, Sum(A.Total) AS Spent, Null As Allocated, Null As CurrentAllocated 
FROM (
SELECT Accounts.accCategory, Accounts.ID, Accounts.comment AS Account, Sum(IIf(Items.idate>=#07/01/2014 00:00:00# AND Items.idate<=#07/09/2014 00:00:00#,Items.amount,Null)) AS Total FROM Accounts INNER JOIN Items ON Accounts.ID = Items.accFrom WHERE (((Items.category)<>3 Or (Items.category) Is Null) AND ((Accounts.accCategory)=6) AND ((Accounts.curr)=1) AND ((Accounts.deleted)=False) AND ((Accounts.extra)<>'B')) GROUP BY Accounts.accCategory, Accounts.ID, Accounts.comment 
UNION SELECT Accounts.accCategory, Accounts.ID, Accounts.comment AS Account, Null FROM Accounts INNER JOIN Items ON Accounts.ID = Items.accFrom WHERE (((Accounts.accCategory)=6) AND ((Accounts.curr)=1) AND ((Accounts.deleted)=False) AND ((Accounts.extra)<>'B') AND Items.idate>=#07/01/2014 00:00:00# AND Items.idate<=#07/09/2014 00:00:00#) 
GROUP BY Accounts.accCategory, Accounts.ID, Accounts.comment) 
AS A 
GROUP BY A.accCategory, A.ID, A.Account 
UNION 
SELECT B.accCategory, B.ID, B.Account, Null as Spent, Sum(B.Total) AS Allocated, Null As CurrentAllocated 
FROM (
SELECT Accounts.accCategory, Accounts.ID, Accounts.comment AS Account, Sum(IIf(Items.idate>=#07/01/2014 00:00:00# AND Items.idate<=#07/09/2014 00:00:00#,Items.amount,Null)) AS Total FROM Accounts INNER JOIN Items ON Accounts.ID = Items.accFrom WHERE ((Items.comment)='Monthly') And ((Items.category) = 3) And ((Accounts.accCategory) = 6) And (Accounts.curr) = 1 And (Accounts.deleted)=False AND (Accounts.extra)<>'B' 
GROUP BY Accounts.accCategory, Accounts.ID, Accounts.comment 
UNION 
SELECT Accounts.accCategory, Accounts.ID, Accounts.comment AS Account, Null FROM Accounts INNER JOIN Items ON Accounts.ID = Items.accFrom WHERE (((Accounts.accCategory)=6) AND ((Accounts.curr)=1) AND ((Accounts.deleted)=False) AND ((Accounts.extra)<>'B') AND Items.idate>=#07/01/2014 00:00:00# AND Items.idate<=#07/09/2014 00:00:00#) GROUP BY Accounts.accCategory, Accounts.ID, Accounts.comment
) AS B 
GROUP BY B.accCategory, B.ID, B.Account 
UNION 
SELECT D.accCategory, D.ID, D.Account, Null as Spent, Null AS Allocated, Sum(D.Total) As CurrentAllocated 
FROM (
SELECT Accounts.accCategory, Accounts.ID, Accounts.comment AS Account, Sum(IIf(Items.idate>=#07/01/2014 00:00:00#,Items.amount,Null)) AS Total FROM Accounts INNER JOIN Items ON Accounts.ID = Items.accFrom WHERE ((Items.comment)='Monthly') And ((Items.category) = 3) And ((Accounts.accCategory) = 6) And (Accounts.curr) = 1 And (Accounts.deleted)=False AND (Accounts.extra)<>'B' 
GROUP BY Accounts.accCategory, Accounts.ID, Accounts.comment 
UNION 
SELECT Accounts.accCategory, Accounts.ID, Accounts.comment AS Account, Null FROM Accounts INNER JOIN Items ON Accounts.ID = Items.accFrom WHERE (((Accounts.accCategory)=6) AND ((Accounts.curr)=1) AND ((Accounts.deleted)=False) AND ((Accounts.extra)<>'B') AND Items.idate>=#07/01/2014 00:00:00#) GROUP BY Accounts.accCategory, Accounts.ID, Accounts.comment
) AS D 
GROUP BY D.accCategory, D.ID, D.Account;

Well, that is an huge block of code that i'm not going to bother to parse, but I believe that your problem lies in the types you are returning in your columns. If you select a date in your first row, then try to union an integer to it, it will throw an error like that. — crthompson, Jul 09 '14 at 22:30
Try to reduce your query to find the culprit. Try `A ... UNION B ...` first. If you still get the error, reduce the queries field for field. — VMai, Jul 09 '14 at 22:37
I have tried to reduce - any combination of A+B A+D B+D works but not all three. — Andy Powell, Jul 09 '14 at 22:46

score 1 · Accepted Answer · answered Jul 09 '14 at 23:15

Thanks for pointing me in the right direction. The problem was the Nulls. If I add the following then all is OK.

SELECT Z.accCategory, Z.ID, Z.Account, 0 as Spent, 0 AS Allocated, 0 As CurrentAllocated 
FROM (
SELECT Accounts.accCategory, Accounts.ID, Accounts.comment AS Account, 0 AS Total FROM Accounts INNER JOIN Items ON Accounts.ID = Items.accFrom WHERE Items.ID=0
) AS Z 
GROUP BY Z.accCategory, Z.ID, Z.Account
UNION
...

Andy

Data mismatch error in multiple UNION SQL Jet query

1 Answers1