Problem in Query and DSUM function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem in Query and DSUM function

Author

Topic

simplefahad2005
Starting Member

2 Posts

Posted - 2012-07-23 : 01:11:44

Hi All,
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.

My query is below kindly help me.
Thanks in advance.

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;

Muhammad Fahad

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-23 : 07:12:21

Are you trying to compose a query in your client application to be sent to a SQL Server? The syntax doesn't look like it is SQL - for example, this:

	[Stock_Card]![Cost] * [Pieces1] AS PurchaseAmount,

That aside, what are you trying to calculate in DSum? Are you trying to aggregate some type of stock on hand? If you can write the forumla that you are trying to implement, there are many people on this forum who would be able to suggest the right way to do it.

(Assuming you are using SQL Server. If you are using another RDBMS, it would be better to ask in a forum specific to that system, or in a more generalized forum such as dbforums.com).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 10:19:13

is DSUM a user defined function?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

simplefahad2005
Starting Member

2 Posts

Posted - 2012-07-27 : 02:28:07

No it is access function. I didnot use sql server am trying to do it in access.

Muhammad Fahad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 02:34:20