DSUM to create Running Sum

[madcats]

I am trying to create a running total in a query. I googled and found something using DSUM, it appeared to be what I wanted but I only get #Error in the field, not running totals.

I want to have a running total for each InvtID that resets on each new InvtID. The InvTranQuery totals by InvtID and TranDate so these are all unique records.

Here is the SQL query I have:

SELECT [InvTran Query].InvtID, [InvTran Query].TranDate, [InvTran Query].SumOfQty, DSum("[SumOfQty]","InvTran Query","[InvtID]=" & [InvtID] & " And [TranDate] <=#" & [TranDate] & "#") AS RSum

FROM [InvTran Query]

ORDER BY [InvTran Query].InvtID, [InvTran Query].TranDate;

Results wanted:
WID1 01/01/2017 10 10
WID1 01/02/2017 20 30
WID1 01/05/2017 -5 25
WID2 01/01/2017 15 15
WID2 01/03/2017 -3 12
WID3 01/05/2017 11 11
WID3 01/06/2017 -2 9

 

[jdraw]

See if this helps

 

[madcats]

Thanks for the quick response, I thought I had some syntax wrong. Looks like I wasn't even close.

 

[pbaldy]

I think you were close. Your ID is text, so would require delimiters. I'm curious if this works:

DSum("[SumOfQty]","[InvTran Query]","[InvtID]='" & [InvtID] & "' And [TranDate] <=#" & [TranDate] & "#")

 

[madcats]

Looks like that is similar to what I am doing. The statement I used was from another site. Can someone tell me if this statement is wrong.

 

[pbaldy]

What statement? Did you try what I posted?

 

[madcats]

I think you were close. Your ID is text, so would require delimiters. I'm curious if this works:

DSum("[SumOfQty]","[InvTran Query]","[InvtID]='" & [InvtID] & "' And [TranDate] <=#" & [TranDate] & "#")

pbaldy,
Yes it did work!!, and just as I wanted, THANK YOU

 

[madcats]

pbaldy,
As I posted, your fix did work. The only thing I just noticed was that it returned the running sum as a text field. My workaround was to create another field and divide the running sum by 1. This seems to work, but just wondering if there was another way to handle this.

 

[pbaldy]

Happy to help! Not sure why it returns text. The field being summed is numeric?

 

[curtyrut]

Hello. Can someone explain why my Dsum formula does not work when I change the date parameters to a different year? For example, the following formula "(DSum("[Eligible Wages]","[401K Report - Dina Detail w running totals]","[Employee ID]='" & [Employee ID] & "' and [Pay Group]='" & [Pay Group] & "'and [Payment Date or Reversal Date]>=#" & [Payment Date or Reversal Date] & "#")*1) " works fine when the date range selected is 2017 yet when I change the year to 2016 nothing populates. In addition, for 2017 the first record for each employee will not populate with a total.
My bad on the long post.

Thank you.

 

[pbaldy]

If it works with one date and not with another, my first thought would be that no data meets the criteria. Does any?

 

[curtyrut]

Hello Pbaldy, I believe I figured out what was causing the data to not populate. I was not aware that you must save your changes before rerunning the query. For example, the database was only recognizing the last date parameters save. Once I change the date parameters, save, then rerun the query the results are as expected. If I change the date parameters without saving, the results are null.

 

[pbaldy]

Glad you got it sorted.