asked on

Crosstab query question

I have created a crosstab query which work fine and displays all records. But in the query designer I want to set a criteria using a year number (like 2013) which I'm getting from a form combobox.

Here is the query criteria:

[Forms]![frmSelectYearForMonthlySumReports]![cboYear]

But it doesn't work because that field on the query is:

Format([DateWorked],"mmm")

What do I have to change in the criteria line?

--Steve

PatHartman

You can't use this field since it has already been formatted to be a month name. Open the query in SQL View and add this to the WHERE clause -

Where ....
AND [Forms]![frmSelectYearForMonthlySumReports]![cboYear] = Year([DateWorked]);

There is no need to add the additional column to the select clause.

SteveL13

ASKER

There is no where clause to add this to. Here is the SQL:

TRANSFORM Sum(qryMonthlySumReportPublicHrs.TotPublicHours) AS SumOfTotPublicHours
SELECT qryMonthlySumReportPublicHrs.DocentID, qryMonthlySumReportPublicHrs.LastName, qryMonthlySumReportPublicHrs.FirstName, tblDocents.AssignedDay, Sum(qryMonthlySumReportPublicHrs.TotPublicHours) AS [Total Of TotPublicHours]
FROM qryMonthlySumReportPublicHrs LEFT JOIN tblDocents ON qryMonthlySumReportPublicHrs.DocentID = tblDocents.DocentID
GROUP BY qryMonthlySumReportPublicHrs.DocentID, qryMonthlySumReportPublicHrs.LastName, qryMonthlySumReportPublicHrs.FirstName, tblDocents.AssignedDay
PIVOT Format([DateWorked],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial