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

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

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?

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

Quote:

---

Originally Posted by baja_yu

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

---

Tks.

Quote:

---

Originally Posted by baja_yu

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
http://i2.photobucket.com/albums/y4/.../salesfig1.jpg

2. Run SQL from VB
http://i2.photobucket.com/albums/y4/...t/salefig2.jpg

Le Van Duyet

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%'

Hi Si_the_geek,

Thanks,

LVD