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

martinr

Registered User.
Local time
Tomorrow, 04:48
Joined
Nov 16, 2011
Messages
66
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

You've got your good things, and you've got mine.
Local time
Today, 14:48
Joined
May 21, 2018
Messages
3,131
I am guessing, but that looks way wrong
Code:
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

AWF VIP
Local time
Today, 14:48
Joined
Oct 20, 2018
Messages
2,704
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

You've got your good things, and you've got mine.
Local time
Today, 14:48
Joined
May 21, 2018
Messages
3,131
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]
 

plog

Banishment Pending
Local time
Today, 13:48
Joined
May 11, 2011
Messages
9,805
...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

Registered User.
Local time
Tomorrow, 04:48
Joined
Nov 16, 2011
Messages
66
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

Banishment Pending
Local time
Today, 13:48
Joined
May 11, 2011
Messages
9,805
Others on this thread discussed the method for that prior to me joining it. The method is the same, just the field name changes.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom