By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 755 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

Problem with date in CrossTab query

P: n/a
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

Jul 13 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.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
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
Jul 13 '08 #2

P: n/a
On Jul 13, 5:46*pm, "Rick Brandt" <rickbran...@hotmail.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.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

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
Jul 13 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.