Totals by date range instead of month 1

[netrusher]

Below is the sql code I have to show me totals of individual page count of work being performed by month and year. What I would like to do is not have the month and year but to change in to a date range like "between 07/23/07 and 07/30/07. Can someone help show me what to change and what to get rid of?

SELECT DSum("OMSDailyPages","DailyOmsWorkTbl","OmsWorkerAssigned='abajacks' AND month([currentdate])=month(now) and year([currentdate])=year(now)") AS [Allen Jackson], DSum("OMSDailyPages","DailyOmsWorkTbl","OmsWorkerAssigned='abchill' AND month([currentdate])=month(now) and year([currentdate])=year(now)") AS [Corene Hilll], DSum("OMSDailyPages","DailyOmsWorkTbl","OmsWorkerAssigned='abmhende' AND month([currentdate])=month(now) and year([currentdate])=year(now)") AS [Marc Henderson], DSum("OMSDailyPages","DailyOmsWorkTbl","OmsWorkerAssigned='abcallen' AND month([currentdate])=month(now) and year([currentdate])=year(now)") AS [Chris Allen], DSum("OMSDailyPages","DailyOmsWorkTbl","month([currentdate])=month(now) and year([currentdate])=year(now)") AS Total
FROM DailyOmsWorkTbl
GROUP BY DSum("OMSDailyPages","DailyOmsWorkTbl","month([currentdate])=month(now) and year([currentdate])=year(now)"), Month([CurrentDate]), Year([CurrentDate])
HAVING (((Month([CurrentDate]))=Month(Now())) AND ((Year([CurrentDate]))=Year(Now())));

 

[PHV]

Why all this DSum ?
What about this ?

SELECT Sum(IIf(OmsWorkerAssigned='abajacks', OMSDailyPages, 0)) AS [Allen Jackson]
, Sum(IIf(OmsWorkerAssigned='abchill', OMSDailyPages, 0)) AS [Corene Hilll]
, Sum(IIf(OmsWorkerAssigned='abmhende', OMSDailyPages, 0)) AS [Marc Henderson]
, Sum(IIf(OmsWorkerAssigned='abcallen', OMSDailyPages, 0)) AS [Chris Allen]
, Sum(OMSDailyPages) AS Total
FROM DailyOmsWorkTbl
WHERE CurrentDate Between #2007-07-23# And #2007-07-30#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

That looks great and works great!

Thanks for the help!

 

[netrusher]

PH

The code below is similar to my earlier posting. I have tried to change it based on the code you supplied earlier but I have not been successfuly. Could you please advise me on changing the query below to what you posted. I am just getting totals on three different pieces on a form and I would like to do it by date range instead of by the month. Thanks for all help.

SELECT DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)") AS FORMS, DCount("[Date_of_Change]","all_trucks_table","[PQC #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)") AS PQC, DCount("[Date_of_Change]","all_trucks_table","[ECN #]=True AND Month([Date_of_Change])=month(now) AND Year([Date_Of_change]) = year(now)") AS ECN, DCount("[Date_of_Change]","all_trucks_table","[MCN #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)") AS MCN
FROM all_trucks_table
GROUP BY DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)"), DCount("[Date_of_Change]","all_trucks_table","[PQC #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)"), DCount("[Date_of_Change]","all_trucks_table","[ECN #]=True AND Month([Date_of_Change])=month(now) AND Year([Date_Of_change]) = year(now)"), DCount("[Date_of_Change]","all_trucks_table","[MCN #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)");