crosstab query (criteria does not work)

[Rich P]

I can create the crosstab query, put I cannot retrieve any data when I put in [enter date] in the criteria area (pick effective date). I read about putting that statement in the Query Parameters, tried that and it did not work. If I type 12/12/2010 it returns the data, but when I type in 01/01/2011, it returns the information for 12/12/2010. Here is the sql:



TRANSFORM Count(tblMAIN.COUNT) AS CountOfCOUNT
SELECT tblEmployee.BADGE, tblEmployee.NAME, tblMAIN.[PICK EFFECTIVE DATE]
FROM tblEmployee INNER JOIN tblMAIN ON tblEmployee.BADGE = tblMAIN.BADGE
GROUP BY tblEmployee.BADGE, tblEmployee.NAME, tblMAIN.[PICK EFFECTIVE DATE]
ORDER BY tblEmployee.BADGE
PIVOT tblMAIN.[CLASS WORKED];

[jzwp11]

What I would do is to create a query that does the filtering and then use that query as the record source for your cross tab.


SELECT tblEmployee.BADGE, tblEmployee.NAME, tblMAIN.[PICK EFFECTIVE DATE]
FROM tblEmployee INNER JOIN tblMAIN ON tblEmployee.BADGE = tblMAIN.BADGE
WHERE tblMAIN.[PICK EFFECTIVE DATE] = [Enter Date]