DSum not calculating the "correct" value

[loopa]

Hi all

I'm battling with DSum to sum the number of sales within a rolling 3 month period (the latest three months).

My formula is as follows...

RunSum: DSum("Sales","Table1","[MthdtSale]>=#" & DateAdd("m",-2,[MthdtSale]) & "# AND [MthdtSale]<=#" & [MthdtSale] & "#")

...which I was hoping translated in to sum all "Sales" in "Table1" for when the months in which sales took place are between two months ago and within the latest sales month. So If MthdtSale was equal to 01/02/2017, I'd expect my DSum to return a value of the sales in both Jan-17 and Feb-17 (there will be sales in later months too, so important to specify a date range).

Even when I use...

RunSum: DSum("Sales","Table1","[MthdtSale]>=#" & DateAdd("m",-2,[MthdtSale]) & "#")

It doesn't sum all sales after a given date - if the date was 01/04/2017, it seems to be summing everything from three months ago...not two.

Issues with DSum are quite common it seems (loads of articles online!!), but I haven't managed to amend the query as necessary to get it working as intended.

Please help...it's driving me mad!

 

[xenou]

Hi,
this is not clear:

...which I was hoping translated in to sum all "Sales" in "Table1" for when the months in which sales took place are between two months ago and within the latest sales month. So If MthdtSale was equal to 01/02/2017, I'd expect my DSum to return a value of the sales in both Jan-17 and Feb-17 (there will be sales in later months too, so important to specify a date range).

You say you want a three month rolling period. But your example only has sales from two months.

Can you give a more exact explanation of what period you want summed in the result, with a new example?

 

[loopa]

Sorry...thought one thing, partly wrote another! It's for a rolling 3 month period.

My text should have read...

So If MthdtSale was equal to 01/02/2017, I'd expect my DSum to return a value of the sales in Dec-16, Jan-17 and Feb-17.

Thanks.

 

[xenou]

Okay, so looking at this you have a big problem in the formula, which is that you are not comparing the dates in the table to anything (other than themselves). You need a date to compare to:

=DSum("Sales","Table1","[MthdtSale]>=#" & DateAdd("m",-2,[MthdtSale]) & "#")

Instead expecting something like:

=DSum("Sales","Table1","[MthdtSale]>=#" & DateAdd("m",-2,[B][COLOR="#FF0000"]Date()[/COLOR][/B]) & "#")

As is, yes you will get all records in the table because yes every date is at least greater than or equal to a date 2 months earlier than itself.

 

[xenou]

Here's an example but see my previous post for the first (main) problem:

DSum
(
"[Sales]",
"[Table8]",
"[MthDtSale]>=DateSerial(year(#2/1/2017#),Month(#2/1/2017#)-2, 1) And [MthDtSale]<DateSerial(year(#2/1/2017#),Month(#2/1/2017#)+1,1)"
)

Result is 1022

Sample data used for the test:
---------------------
| Sales | MthDtSale |
---------------------
|     1 | 30-Nov-16 |
|     2 | 01-Dec-16 |
|     4 | 15-Dec-16 |
|     8 | 31-Dec-16 |
|    16 | 01-Jan-17 |
|    32 | 15-Jan-17 |
|    64 | 31-Jan-17 |
|   128 | 01-Feb-17 |
|   256 | 15-Feb-17 |
|   512 | 28-Feb-17 |
|  1024 | 01-Mar-17 |
|  2048 | 15-Mar-17 |
|  4096 | 31-Mar-17 |
|  8192 | 01-Apr-17 |
---------------------

 

[loopa]

Thanks for your response.

However, since I want to calculate the rolling 3 months sum for every record in the table, using specific dates in the DSum won't work...since the DSum needs to look at the MthDtSale for each record and calculate the 3 months sum from this date. The date in MthDtSale will always be the 1st of the month by the way.

What you've proposed in your example would show 1022 for every record...wouldn't it?

Thanks.

 

[xenou]

I see, you want a running sum. I don't think that you can use DSum() then. Simplest is to use Access reports which have a running sum feature. Otherwise you have to write a query that uses a subquery.