Using the dateadd function within DSUM

[Guest]

I am trying to use the DSM function to create a rolling sum within "Query 1"
using the following dataset.

Production date Value
01-Jan-03 120
01-Feb-03 50
01-Mar-03 75
01-Apr-03 0
01-May-03 65
01-Jun-03 122
01-Jul-03 75
01-Aug-03 95
01-Oct-03 66
01-Nov-03 99
01-Dec-03 67
01-Jan-04 132
01-Feb-04 87

Using

Rolling: DSum([Value], "[Prod]" ,"[Production date] >" &
DateAdd("m",-12,[Production date]))

the answer for January 2003 is 2280 instead of 120. What should I do to
create the accurate DSUM value to create a rolling sum for 12 months woth of
data?

 

[Duane Hookom]

You could try a subquery like:
Rolling: (SELECT Sum([Value]) FROM Prod P WHERE P.[Production Date] Between
DateAdd("yyyy",-1,Prod.[Production Date] AND Prod.[Production Date])

 

[Guest]

Thanks for the help.

You could try a subquery like:
Rolling: (SELECT Sum([Value]) FROM Prod P WHERE P.[Production Date] Between
DateAdd("yyyy",-1,Prod.[Production Date] AND Prod.[Production Date])

--
Duane Hookom
MS Access MVP

I am trying to use the DSM function to create a rolling sum within "Query
1"
using the following dataset.

Production date Value
01-Jan-03 120
01-Feb-03 50
01-Mar-03 75
01-Apr-03 0
01-May-03 65
01-Jun-03 122
01-Jul-03 75
01-Aug-03 95
01-Oct-03 66
01-Nov-03 99
01-Dec-03 67
01-Jan-04 132
01-Feb-04 87

Using

Rolling: DSum([Value], "[Prod]" ,"[Production date] >" &
DateAdd("m",-12,[Production date]))

the answer for January 2003 is 2280 instead of 120. What should I do to
create the accurate DSUM value to create a rolling sum for 12 months woth
of
data?