Problem in Query and DSUM function

Question

I am using this query regarding stock card in my application but it is giving error undefined dsum expression. please help me in this regards. Thanks in Advance.
Query is mentioned below.


SELECT Stock_Card.StockCardID AS StockCardIDAlias, Stock_Card.StockID AS StockIDAlias, Stocks.Stock, Stock_Card.DateInsert, Stock_Card.ID, Stock_Card.Type, Stock_Card.RefNo1, Stock_Card.Pieces1, Stock_Card.Cost, [Stock_Card]![Cost]*[Pieces1] AS PurchaseAmount, Vendors.Company AS vendor, Stock_Card.RefNo2, Stock_Card.Pieces2, Stock_Card.SalesPrice, [Stock_Card]![SalesPrice]*[Pieces2] AS SalesAmount, Clients.Company AS client, Stock_Card.ICode,
DSum("[Pieces1]-[Pieces2]","Stock_Card","[StockCardID] <" & [StockCardIDAlias] & " And [StockID] = " & [StockIDAlias] & "") AS Previous, DSum("[Pieces1]-[Pieces2]","Stock_Card","[StockCardID] <=" & [StockCardIDAlias] & " And [StockID] = " & [StockIDAlias] & "") AS [On Hand]
FROM Clients RIGHT JOIN (Vendors RIGHT JOIN (Stock_Card LEFT JOIN Stocks ON Stock_Card.StockID = Stocks.StockID) ON Vendors.VendorId = Stock_Card.VendorID) ON Clients.ClientID = Stock_Card.ClientID;

Answer 1

What if you use

SELECT Stock_Card.StockCardID AS StockCardIDAlias, Stock_Card.StockID AS StockIDAlias, Stocks.Stock, Stock_Card.DateInsert, Stock_Card.ID, Stock_Card.Type, Stock_Card.RefNo1, Stock_Card.Pieces1, Stock_Card.Cost, [Stock_Card]![Cost]*[Pieces1] AS PurchaseAmount, Vendors.Company AS vendor, Stock_Card.RefNo2, Stock_Card.Pieces2, Stock_Card.SalesPrice, [Stock_Card]![SalesPrice]*[Pieces2] AS SalesAmount, Clients.Company AS client, Stock_Card.ICode,
DSum("[Pieces1]-[Pieces2]","Stock_Card","[StockCardID] <" & [Stock_Card].[StockCardID] & " And [StockID] = " & [Stock_Card].[StockID]) AS Previous, DSum("[Pieces1]-[Pieces2]","Stock_Card","[StockCardID] <=" & [Stock_Card].[StockCardID] & " And [StockID] = " & [Stock_Card].[StockID]) AS [On Hand]
FROM Clients RIGHT JOIN (Vendors RIGHT JOIN (Stock_Card LEFT JOIN Stocks ON Stock_Card.StockID = Stocks.StockID) ON Vendors.VendorId = Stock_Card.VendorID) ON Clients.ClientID = Stock_Card.ClientID;

---

Regards, Hans Vogelaar

Answer 2

There are various bits of the SQL query that don't understand the aliases (just the underlying fields, because that's what they actually deal with). The DSum function (or any other Access-specific function) will suffer from this limitation. As Hans V has demonstrated, it's not hard to work around this.

---

Matthew Slyman M.A. (Camb.)