Problem in Query and DSUM function

# Problem in Query and DSUM function

• 2012년 7월 27일 금요일 오전 9:33

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;

### 모든 응답

• 2012년 7월 27일 금요일 오전 9:51

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

• 2012년 7월 27일 금요일 오전 10:50

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.)