473,287 Members | 1,960 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Problem with date in CrossTab query

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
2 3368
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Steve Elliott | last post by:
I have a query set up to gather together data between two specified dates. Shown in the query column as: Between #24/09/2004# And #01/10/2004# Is it possible to enter several different date...
2
by: Kevin | last post by:
Hi I have created a report based on a Crosstab query the crosstab query is based on another query problem i have is that the results this month may be different next month having tested this by...
2
by: carl.barrett | last post by:
Hi, I'm back with the same question as I still can't get it to display my data the way I want it to. The table lists information about a perpetrator involved with an anti social behaviour...
3
by: russellhq | last post by:
Hi, I'm fairly new to access and have a little trouble with a crosstab query I've setup. I have a main form where the user selects a project name and below in a subform, a crosstab query is...
1
by: devyon122 | last post by:
I'm trying to allow a user to type in a beginning data and an ending date to be used in a crosstab query - Below is the query: TRANSFORM Count(imagingdb.ID) AS CountOfID TRANSFORM...
4
by: mattlightbourn | last post by:
Hi all, I have a problem which has been driving me nuts. Crosstab queries! I have a database witch a few different tables to do with garment manufacturing. I have a table for a client...
7
by: sheri | last post by:
I have a field called date, the date is in the field like this 70925 (which means Sept. 25, 2007). I have another field called day, it is a text field. How do I write a query to populate the day...
4
by: odavison | last post by:
I'm currently running a crosstab query that displays the total amounts of Appointments that each Consultant. Currently it is Consultant ID as the Rows, and Appointment Date as the top headings,...
5
by: HowHow | last post by:
First time using crosstab query because tired of creating queries and put them together in one query (such a stupid thing to do :D). Not sure how it works still. I have link table called...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.