help needed using Dsum for running total in a query (1 Viewer)

[martinr]

i have a Transactions table for recording product Sales and Purchases and want a Query to show a running balance for sales and purchases for each product.
i'm getting an error when trying to use the Dsum function below: (Type is product type and Tdate is transaction date)

balance: DSum("Purchased"," [TblTransactions]","[TblTransactions]![Type]"=[Type] And "[TblTransactions]![Tdate]"<=[Tdate])

is there a better way to to do this?

 

[MajP]

I am guessing, but that looks way wrong

alance: DSum("Purchased","TblTransactions","Type = '" & me.Type & "' And Tdate <= #" & format(me.Tdate,"MM/dd/yyyy") & "#")

Field is purchased
table is tblTransactions
Type is a text field and you are pulling the value from the current form
Tdate you are pulling from current form

 

[Micron]

I think the expression is a calculated field in a query, which means you cannot use Me?

i'm getting an error when trying to use the Dsum function below

Forgot to say, always best to give the error message (at least) and the number is nice to have. If it's a block of code, the line that raises the error too. Number only is not usually of much help. I've stopped looking up any of the 3 thousand or so numbers when somebody just posts the number.

 

[MajP]

I think the expression is a calculated field in a query, which means you cannot use Me

Well then it is even worse. Replace Me with [Forms}]![someformName]

 

[martinr]

Yes, just trying to run the query atm - no point building the form until the query is working...

 

[plog]

...Transactions table for recording product Sales and Purchases and want a Query to show a running balance for sales and purchases

The [Balance] field you posted is neither named [PurchasesBalance] nor references the [Sales] field. Where and how are you expecting that field to come into play?

I'm pretty sure you've structured your table incorrectly. Credits and debit amounts need to go into the same field, not into seperate ones. Then, when you need a balance you Sum that one field.

You should put this issue aside and fix that before proceeding.

 

[martinr]

Ok, Thanks for your suggestions.

i have put the Purchases & sales amounts into the same field (-ve amount for Sales)
If i aggregate the transactions by type i can get a sum that is the 'balance' for all transactions for each product type.

How do i create a query to show a running balance/total based on the date for each product (like a daily balance on a bank statement)?

 

[plog]

Others on this thread discussed the method for that prior to me joining it. The method is the same, just the field name changes.