Problem with date in CrossTab query

[Jim Devenish]

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.EnquirySo urceID, Tasks.BusinessU nitID,
Count(Tasks.Tas kID) AS CountOfTaskID
FROM Tasks
WHERE (((Tasks.TaskDa te)>=[Forms]![SalesStatistics Popup]![fromDate]))
GROUP BY Tasks.EnquirySo urceID, Tasks.BusinessU nitID;

(I have simplified the date selection to be just >= instead of Between
… )

However if I convert this to a CrossTab query:
TRANSFORM Count(Tasks.Tas kID) AS CountOfTaskID
SELECT Tasks.EnquirySo urceID
FROM Tasks
WHERE (((Tasks.TaskDa te)>=[Forms]![SalesStatistics Popup]![fromDate]))
GROUP BY Tasks.EnquirySo urceID
PIVOT Tasks.BusinessU nitID;

I get the error message:
The Microsoft Jet database engine does not recognize “[Forms]!
[SalesStatistics Popup]![fromDate]” as a valid field name or
expression.

How do I create a CrossTab query using a date entered by the user?

Jim

[Rick Brandt]

Re: Problem with date in CrossTab query

Jim Devenish wrote:

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.EnquirySo urceID, Tasks.BusinessU nitID,
Count(Tasks.Tas kID) AS CountOfTaskID
FROM Tasks
WHERE (((Tasks.TaskDa te)>=[Forms]![SalesStatistics Popup]![fromDate]))
GROUP BY Tasks.EnquirySo urceID, Tasks.BusinessU nitID;
>
(I have simplified the date selection to be just >= instead of Between
… )
>
However if I convert this to a CrossTab query:
TRANSFORM Count(Tasks.Tas kID) AS CountOfTaskID
SELECT Tasks.EnquirySo urceID
FROM Tasks
WHERE (((Tasks.TaskDa te)>=[Forms]![SalesStatistics Popup]![fromDate]))
GROUP BY Tasks.EnquirySo urceID
PIVOT Tasks.BusinessU nitID;
>
I get the error message:
The Microsoft Jet database engine does not recognize “[Forms]!
[SalesStatistics Popup]![fromDate]” as a valid field name or
expression.
>
How do I create a CrossTab query using a date entered by the user?
>
Jim

Unlike normal queries, crosstab queries insist that all parameters be
explicitly defined and given a DataType. In design view of the query
right-click on the background of the table area and choose "Parameters " from
the resulting menu (or find this in the query menubar).

In the parameters dialog enter each of your parameters and fill in the
DataType column. You should find that the parameters now work.

Interestingly, even when you have a working parameter query that does not
need to have the parameters explicitly defined you will have to do so if you
use that query as the input to a crosstab query. Sometimes you can get this
message several layers removed from the query you are actually working on.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Jim Devenish]

Re: Problem with date in CrossTab query

On Jul 13, 5:46 pm, "Rick Brandt" <rickbran...@ho tmail.comwrote:

Jim Devenish wrote:

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.EnquirySo urceID, Tasks.BusinessU nitID,
Count(Tasks.Tas kID) AS CountOfTaskID
FROM Tasks
WHERE (((Tasks.TaskDa te)>=[Forms]![SalesStatistics Popup]![fromDate]))
GROUP BY Tasks.EnquirySo urceID, Tasks.BusinessU nitID;

>

(I have simplified the date selection to be just >= instead of Between
… )

>

However if I convert this to a CrossTab query:
TRANSFORM Count(Tasks.Tas kID) AS CountOfTaskID
SELECT Tasks.EnquirySo urceID
FROM Tasks
WHERE (((Tasks.TaskDa te)>=[Forms]![SalesStatistics Popup]![fromDate]))
GROUP BY Tasks.EnquirySo urceID
PIVOT Tasks.BusinessU nitID;

>

I get the error message:
The Microsoft Jet database engine does not recognize “[Forms]!
[SalesStatistics Popup]![fromDate]” as a valid field name or
expression.

>

How do I create a CrossTab query using a date entered by the user?

>

Jim

>
Unlike normal queries, crosstab queries insist that all parameters be
explicitly defined and given a DataType.  In design view of the query
right-click on the background of the table area and choose "Parameters " from
the resulting menu (or find this in the query menubar).
>
In the parameters dialog enter each of your parameters and fill in the
DataType column.  You should find that the parameters now work.
>
Interestingly, even when you have a working parameter query that does not
need to have the parameters explicitly defined you will have to do so if you
use that query as the input to a crosstab query.  Sometimes you can getthis
message several layers removed from the query you are actually working on..
>
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com

Thanks Rick, that did the trick

Jim