I wish to create a crosstab query as the record source for a report.
It needs to count data between selected dates which are entered by the
user in a popup window.
The following Select query works:
SELECT Tasks.EnquirySourceID, Tasks.BusinessUnitID,
Count(Tasks.TaskID) AS CountOfTaskID
FROM Tasks
WHERE (((Tasks.TaskDate)>=[Forms]![SalesStatisticsPopup]![fromDate]))
GROUP BY Tasks.EnquirySourceID, Tasks.BusinessUnitID;
(I have simplified the date selection to be just >= instead of Between
… )
However if I convert this to a CrossTab query:
TRANSFORM Count(Tasks.TaskID) AS CountOfTaskID
SELECT Tasks.EnquirySourceID
FROM Tasks
WHERE (((Tasks.TaskDate)>=[Forms]![SalesStatisticsPopup]![fromDate]))
GROUP BY Tasks.EnquirySourceID
PIVOT Tasks.BusinessUnitID;
I get the error message:
The Microsoft Jet database engine does not recognize “[Forms]!
[SalesStatisticsPopup]![fromDate]” as a valid field name or
expression.
How do I create a CrossTab query using a date entered by the user?
Jim