How to translate SQL language in Access to SQL in VBA, VB

[levanduyet]

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.

(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

[baja_yu]

Your question is better suited in the Database Development section. I'll notify Mods to consider moving it there.

Also, what do you mean by "the result was wrong" in VB? What results do you expect and what are the results you get?

[westconn1]

you need to quote your strings, but not your variables or objects

[levanduyet]

Your question is better suited in the Database Development section. I'll notify Mods to consider moving it there.

Tks.

Also, what do you mean by "the result was wrong" in VB? What results do you expect and what are the results you get?

I mean that I took the SQL string in Access, then use it in the VBA to extract the data from Access. But the the result is difference as the pictures:
1. Run SQL from Access


2. Run SQL from VB


Le Van Duyet

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

The most likely thing to need to change is the wildcard for Like, rather than * try using % , eg: LIKE '22%'

[levanduyet]

Hi Si_the_geek,

Thanks,

LVD