CODEWITHSUNDEEP
ms-access
Chacal33
Chacal33

Reputation: 1

DSum for cumulative sum with 2 criteria

I am running an Access query with a DSum formula to get a cumulative sum based on 2 criteria.

Here is the base query:

ID Date Value
2 08/02/2021 4000
2 15/02/2021 200
2 25/01/2021 500

I have tried the following formula:

DSum("[Value]","[Query]","[ID]=" & [ID] & " and [Date]<=#" & [Date] & "#")

And here is the outcome vs the expected:

ID Date Value Expected
2 08/02/2021 4000 4000
2 15/02/2021 4200 4200
2 25/01/2021 4200 4700

If anyone could help me understand why i do not get the last operation, I would greatly appreciate it.

Many thanks

Matt

Upvotes: 0

Views: 796

Answers (1)

Gustav
Gustav

Reputation: 55816

Try this using a proper string expression for the date:

DSum("[Value]","[Query]","[ID]=" & [ID] & " And [Date]<=#" & Format([Date], "yyyy\/mm\/dd") & "#")

Upvotes: 1

Related Questions