DSum criteria

[Jamin]

Hi,

I have an Dsum-function which is based on an date criteria.
The problem is that the Dsum-function sums up everything
in the table. It should only sum up the last 12 entries
(which means the last 12 month, also if the year changes)

Can anybody help ?

Jamin


PS: My Query
Datum is splittet in AMonth and AYear
SELECT DatePart("yyyy",[Leiharbeiterproduktion]![Datum])
AS AYear, DatePart("m",[Leiharbeiterproduktion]![Datum])
AS AMonth, Val(Nz(DSum
("Stückzahl","Leiharbeiterproduktion","DatePart('m',
[Leiharbeiterproduktion]![Datum])<=" & [AMonth] & " And
DatePart('yyyy',[Leiharbeiterproduktion]![Datum])<=" &
[AYear] & ""),"")) AS T, DatePart("yyyy",
[Mitarbeiterproduktion]![Datum]) AS A1Year, DatePart("m",
[Mitarbeiterproduktion]![Datum]) AS A1Month, Val(Nz(DSum
("Stückzahl","Mitarbeiterproduktion","DatePart('m',[Datum])
<=" & [A1Month] & " And DatePart('yyyy',[Datum])<=" &
[A1Year] & ""),"")) AS T1, [T]+[T1] AS Gesamtsumme, Format
([Mitarbeiterproduktion]![Datum],"yyyy/mm/dd") AS Datum1
FROM Mitarbeiterproduktion INNER JOIN
Leiharbeiterproduktion ON Mitarbeiterproduktion.Datum =
Leiharbeiterproduktion.Datum
GROUP BY DatePart("yyyy",[Leiharbeiterproduktion]!
[Datum]), DatePart("m",[Leiharbeiterproduktion]![Datum]),
DatePart("yyyy",[Mitarbeiterproduktion]![Datum]), DatePart
("m",[Mitarbeiterproduktion]![Datum]), Format
([Mitarbeiterproduktion]![Datum],"yyyy/mm/dd")
ORDER BY DatePart("yyyy",[Leiharbeiterproduktion]!
[Datum]), DatePart("m",[Leiharbeiterproduktion]![Datum]),
DatePart("yyyy",[Mitarbeiterproduktion]![Datum]), DatePart
("m",[Mitarbeiterproduktion]![Datum]);

 

[Gary Walter]

Hi Jamin,

Several things come to mind when I look at
your query...2 probably have nothing to do with your problem.

1) why use DatePart

Month(date) Returns a Variant (Integer) specifying a whole number between 1 and 12,
inclusive, representing the month of the year
Year(date) Returns a Variant (Integer) containing a whole number representing the
year

2) one typically uses a "dot" instead of a "bang" (!), unless referencing a form

[Leiharbeiterproduktion].[Datum]

3) these "DSum's" are not part of the Group By
and your criteria range just won't give you the past
12 months...

if AMonth = 8 and AYear = 2004,

you would get records for *all* years <= 2004,
but only months 1,2,3,4,5,6,7 and 8!

suppose
Datum=#8/25/2004#

I think what you want is to get
sum over range 8/1/2003 to 8/31/2004?

?Month(Datum)
8
?Year(Datum)
2004
?DateSerial(Year(Datum),Month(Datum)-12,1)
8/1/2003 (time portion = 12:00 AM)
?DateSerial(Year(Datum),Month(Datum)+1,0)
8/31/2004 (time portion = 12:00 AM)

Val(Nz(DSum
("Stückzahl","Leiharbeiterproduktion","DatePart('m',
[Leiharbeiterproduktion]![Datum])<=" & [AMonth] & " And
DatePart('yyyy',[Leiharbeiterproduktion]![Datum])<=" &
[AYear] & ""),"")) AS T,

what happens if you replace above with
(*untested*)


First(SELECT Sum(L.Stuckzahl)
FROM Leiharbeiterproduktion As L
WHERE L.Datum
BETWEEN
DateSerial([AYear],[AMonth]-12,1)
AND
DateSerial([AYear],[AMonth]+1,0)) As T,


If your Datum contain time values, then the above
would "lose" all values after 12:00 AM on "8/31/2004".

In that case, you would want the ending criteria
to be 12:00 AM of "9/1/2004"

?DateSerial(Year(Datum),Month(Datum)+1,1)
9/1/2004 (time portion = 12:00 AM)

Again, above is untested, but hopefully you
may better understand the process that will work for you.

Good luck,

Gary Walter


:
Hi,

I have an Dsum-function which is based on an date criteria.
The problem is that the Dsum-function sums up everything
in the table. It should only sum up the last 12 entries
(which means the last 12 month, also if the year changes)

Can anybody help ?

Jamin


PS: My Query
Datum is splittet in AMonth and AYear
SELECT DatePart("yyyy",[Leiharbeiterproduktion]![Datum])
AS AYear, DatePart("m",[Leiharbeiterproduktion]![Datum])
AS AMonth, Val(Nz(DSum
("Stückzahl","Leiharbeiterproduktion","DatePart('m',
[Leiharbeiterproduktion]![Datum])<=" & [AMonth] & " And
DatePart('yyyy',[Leiharbeiterproduktion]![Datum])<=" &
[AYear] & ""),"")) AS T, DatePart("yyyy",
[Mitarbeiterproduktion]![Datum]) AS A1Year, DatePart("m",
[Mitarbeiterproduktion]![Datum]) AS A1Month, Val(Nz(DSum
("Stückzahl","Mitarbeiterproduktion","DatePart('m',[Datum])
<=" & [A1Month] & " And DatePart('yyyy',[Datum])<=" &
[A1Year] & ""),"")) AS T1, [T]+[T1] AS Gesamtsumme, Format
([Mitarbeiterproduktion]![Datum],"yyyy/mm/dd") AS Datum1
FROM Mitarbeiterproduktion INNER JOIN
Leiharbeiterproduktion ON Mitarbeiterproduktion.Datum =
Leiharbeiterproduktion.Datum
GROUP BY DatePart("yyyy",[Leiharbeiterproduktion]!
[Datum]), DatePart("m",[Leiharbeiterproduktion]![Datum]),
DatePart("yyyy",[Mitarbeiterproduktion]![Datum]), DatePart
("m",[Mitarbeiterproduktion]![Datum]), Format
([Mitarbeiterproduktion]![Datum],"yyyy/mm/dd")
ORDER BY DatePart("yyyy",[Leiharbeiterproduktion]!
[Datum]), DatePart("m",[Leiharbeiterproduktion]![Datum]),
DatePart("yyyy",[Mitarbeiterproduktion]![Datum]), DatePart
("m",[Mitarbeiterproduktion]![Datum]);