3-year Comparison YTD

[Rosy]

I need to run a report that calculates YTD commissions for the last
three years.

2005 Cargo Dept Inland Dept Hull Dept Total
Jan
Feb

2004 Cargo Dept Inland Dept Hull Dept Total
Jan
Feb

2003 Cargo Dept Inland Dept Hull Dept Total
Jan
Feb

I correctly displays the current year info, but gives me the entire
year for the previous years. How do I get Access to ignore months past
the current month in previous years?

Thanks.

[Randy Harris]

"Rosy" <kr****@portlite.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

I need to run a report that calculates YTD commissions for the last
three years.

2005 Cargo Dept Inland Dept Hull Dept Total
Jan
Feb

2004 Cargo Dept Inland Dept Hull Dept Total
Jan
Feb

2003 Cargo Dept Inland Dept Hull Dept Total
Jan
Feb

I correctly displays the current year info, but gives me the entire
year for the previous years. How do I get Access to ignore months past
the current month in previous years?

Thanks.

One way would be to use DatePart in the query.

WHERE DatePart("m", [PayDate]) <= DatePart("m", Date)

This would ignore months past the current month in previous years.

[Rosy]

Okay, I see what that is supposed to do, but it gives me a data mistype
error. What am I doing wrong?

TRANSFORM Sum([qryComm-3yr].LineTotal) AS SumOfLineTotal
SELECT [qryComm-3yr].MonthClosed, [qryComm-3yr].YearClosed,
Sum([qryComm-3yr].LineTotal) AS Total
FROM tblDepartment INNER JOIN [qryComm-3yr] ON tblDepartment.DeptID =
[qryComm-3yr].DeptID
WHERE ((DatePart("m",[MonthClosed])<=DatePart("m","Date")))
GROUP BY [qryComm-3yr].MonthClosed, [qryComm-3yr].YearClosed
PIVOT tblDepartment.Department;

[Randy Harris]

"Rosy" <kr****@portlite.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

Okay, I see what that is supposed to do, but it gives me a data mistype
error. What am I doing wrong?

TRANSFORM Sum([qryComm-3yr].LineTotal) AS SumOfLineTotal
SELECT [qryComm-3yr].MonthClosed, [qryComm-3yr].YearClosed,
Sum([qryComm-3yr].LineTotal) AS Total
FROM tblDepartment INNER JOIN [qryComm-3yr] ON tblDepartment.DeptID =
[qryComm-3yr].DeptID
WHERE ((DatePart("m",[MonthClosed])<=DatePart("m","Date")))
GROUP BY [qryComm-3yr].MonthClosed, [qryComm-3yr].YearClosed
PIVOT tblDepartment.Department;

WHERE ((DatePart("m",[MonthClosed])<=DatePart("m","Date")))

In this line you are comparing the "month" to the "month" of the current
date, using the Date() function.
Change it to
WHERE ((DatePart("m",[MonthClosed])<=DatePart("m",Date)))
Notice there are no quotes around the Date function.