Dear All,

I have faced the problem that, I have test SQL in database MS.Access. It run correctly but when I use this SQL in VB (or VBA) the result was wrong.

Code:
(SELECT Data.Item, Data.Description, SUM(Data.ProQty) AS [ProQty], SUM(Data.SaleQty) AS [SaleQty], 0 AS [StockQty] 
               FROM (SELECT TB_Movements_Docs_Keep.sMaNo AS [Item], TB_Movements_Docs_Keep.sMaDes AS [Description], 
               TB_Movements_Docs_Keep.nQty AS [ProQty], 0 AS [SaleQty] 
               FROM TB_Movements_Docs_Keep 
               WHERE MONTH(TB_Movements_Docs_Keep.dPosDate)=9 And YEAR(TB_Movements_Docs_Keep.dPosDate)=2010 
              AND (TB_Movements_Docs_Keep.nMovType=131  OR TB_Movements_Docs_Keep.nMovType=132) 
               AND TB_Movements_Docs_Keep.sMaNo LIKE '22*' AND TB_Movements_Docs_Keep.sLoc='P001' 
               ORDER BY TB_Movements_Docs_Keep.sMaNo 
               UNION SELECT DISTINCT TB_Movements_Docs_Keep.sMaNo AS [Item], TB_Movements_Docs_Keep.sMaDes AS [Description], 
               0 AS [ProQty], TB_Movements_Docs_Keep.nQty AS [SaleQty] 
               FROM TB_Movements_Docs_Keep 
               WHERE MONTH(TB_Movements_Docs_Keep.dPosDate)=9 And YEAR(TB_Movements_Docs_Keep.dPosDate)=2010
              AND (TB_Movements_Docs_Keep.nMovType=601 OR TB_Movements_Docs_Keep.nMovType=602 
               OR TB_Movements_Docs_Keep.nMovType=992 OR TB_Movements_Docs_Keep.nMovType=993) 
               AND TB_Movements_Docs_Keep.sMaNo LIKE '22*' AND TB_Movements_Docs_Keep.sLoc='W001')  AS [Data] 
               GROUP BY Data.Item, Data.Description) 
               UNION (SELECT TB_Stock.sMaNo AS [Item], TB_Stock.sMaDes AS [Description], 0 AS [ProQty], 
               0 AS [SaleQty], TB_Stock.nMaQty AS [StockQty] 
               FROM TB_Stock 
               WHERE TB_Stock.sLoc='W001' AND GoodsCondition=1);
Anyone has experience about this problem.

Thanks,

LVD