Strange problem with crosstab query

[Ragnar]

Hello,

I have been scratching my head over this problem so I hope someone can spot
what is wrong.

The CT query is:

TRANSFORM Val(Nz(Count([qryReviews].[Reviews]))) AS ReviewCount
SELECT [qryReviews].[DateRev], Val(Nz(Count([qryReviews].[Reviews]))) AS
DailyTotal
FROM qryReviews
GROUP BY [qryReviews].[DateRev]
PIVOT [qryReviews].[RevInits];

and the query it is selecting from is:

SELECT [Diagnosis CV].[Accession Number] AS Reviews,
[Diagnosis CV].[Date Reviewed] AS DateRev,
[Diagnosis CV].[Reviewer Initials] AS RevInits
FROM [Diagnosis CV]
WHERE
((([Diagnosis CV].[Date Reviewed])>=Forms!frmDateRange!txtStartDate
And ([Diagnosis CV].[Date Reviewed])<=Forms!frmDateRange!txtEndDate));

With the form frmDateRange open, and containing valid start and end dates, I
can run the select query with no problems.
However, when I run the CT query I get an error message that MS Jet database
engine does not recognize Forms!frmDateRange!txtEndDate as a valid field or
expression!

At first I noticed that the QBE had put square brackets around the items in
the date spec, like this [Forms]![frmDateRange]![txtEndDate], so I removed
them, but I still get the error message. I also tried with dots instead of
bangs, in all cases the bottom level query runs fine but the CT complains.

I am really puzzled because I have used excactly this construct, with a CT
query selecting from a subquery where the selection criteria are applied,
countless times and never had a problem.

Any help would be appreciated.

Ragnar

 

[Michel Walsh]

Hi,


You HAVE TO define the exact data type for each parameter in a Crosstab
query. From the query designer, it is in the menu, under Query | Parameters.
Supply the parameters (FORMS!FormName!ControlName) and the expected data
type.



Hoping it may help,
Vanderghast, Access MVP

 

[Ragnar Midtskogen]

Thank you Michel, that fixed the problem!

I wonder how I got away with not defining the parameters before?

Ragnar

Hi,


You HAVE TO define the exact data type for each parameter in a Crosstab
query. From the query designer, it is in the menu, under Query |
Parameters. Supply the parameters (FORMS!FormName!ControlName) and the
expected data type.



Hoping it may help,
Vanderghast, Access MVP

Hello,

I have been scratching my head over this problem so I hope someone can
spot
what is wrong.

The CT query is:

TRANSFORM Val(Nz(Count([qryReviews].[Reviews]))) AS ReviewCount
SELECT [qryReviews].[DateRev], Val(Nz(Count([qryReviews].[Reviews]))) AS
DailyTotal
FROM qryReviews
GROUP BY [qryReviews].[DateRev]
PIVOT [qryReviews].[RevInits];

and the query it is selecting from is:

SELECT [Diagnosis CV].[Accession Number] AS Reviews,
[Diagnosis CV].[Date Reviewed] AS DateRev,
[Diagnosis CV].[Reviewer Initials] AS RevInits
FROM [Diagnosis CV]
WHERE
((([Diagnosis CV].[Date Reviewed])>=Forms!frmDateRange!txtStartDate
And ([Diagnosis CV].[Date Reviewed])<=Forms!frmDateRange!txtEndDate));

With the form frmDateRange open, and containing valid start and end
dates, I
can run the select query with no problems.
However, when I run the CT query I get an error message that MS Jet
database
engine does not recognize Forms!frmDateRange!txtEndDate as a valid field
or
expression!

At first I noticed that the QBE had put square brackets around the items
in
the date spec, like this [Forms]![frmDateRange]![txtEndDate], so I
removed
them, but I still get the error message. I also tried with dots instead
of
bangs, in all cases the bottom level query runs fine but the CT
complains.

I am really puzzled because I have used excactly this construct, with a
CT
query selecting from a subquery where the selection criteria are applied,
countless times and never had a problem.

Any help would be appreciated.

Ragnar

 

[John Spencer (MVP)]

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Repeat for the second parameter.

 

[Ragnar Midtskogen]

Thank you John,

Michel Walsh already posted a not to the same effect, but thank you anyway.
As I responded already to him, I mentioned that I was wondering how I got
away with not declaring parameters for so long. In the very same database
(Access 2000) alone, I have half a dozen CT queries selecting from queries
with form fields as parameters. In these queries none of the parameters are
Date/Time, they are numbers. Just to be sure I had forgotten that I defined
parameters, I checked, and I found that the 'Parameters' item of the Query
menuitem was disabled.
But I have other applications in A2K where I use dates.

Ragnar

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Repeat for the second parameter.

Hello,

I have been scratching my head over this problem so I hope someone can
spot
what is wrong.

The CT query is:

TRANSFORM Val(Nz(Count([qryReviews].[Reviews]))) AS ReviewCount
SELECT [qryReviews].[DateRev], Val(Nz(Count([qryReviews].[Reviews]))) AS
DailyTotal
FROM qryReviews
GROUP BY [qryReviews].[DateRev]
PIVOT [qryReviews].[RevInits];

and the query it is selecting from is:

SELECT [Diagnosis CV].[Accession Number] AS Reviews,
[Diagnosis CV].[Date Reviewed] AS DateRev,
[Diagnosis CV].[Reviewer Initials] AS RevInits
FROM [Diagnosis CV]
WHERE
((([Diagnosis CV].[Date Reviewed])>=Forms!frmDateRange!txtStartDate
And ([Diagnosis CV].[Date Reviewed])<=Forms!frmDateRange!txtEndDate));

With the form frmDateRange open, and containing valid start and end
dates, I
can run the select query with no problems.
However, when I run the CT query I get an error message that MS Jet
database
engine does not recognize Forms!frmDateRange!txtEndDate as a valid field
or
expression!

At first I noticed that the QBE had put square brackets around the items
in
the date spec, like this [Forms]![frmDateRange]![txtEndDate], so I
removed
them, but I still get the error message. I also tried with dots instead
of
bangs, in all cases the bottom level query runs fine but the CT
complains.

I am really puzzled because I have used excactly this construct, with a
CT
query selecting from a subquery where the selection criteria are applied,
countless times and never had a problem.

Any help would be appreciated.

Ragnar

 

[Duane Hookom]

The following crosstab works great without explicit parameters:

TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT Orders.CustomerID
FROM Orders
WHERE (((Orders.OrderDate) Between [Forms]![form1]![txtStart] And
[Forms]![form1]![txtEnd]))
GROUP BY Orders.CustomerID
PIVOT Format([OrderDate],"ddd") In
("Sun","Mon","Tue","Wed","Thu","Fri","Sat");

The reason seems to be the use of specific Column Headings (the In (...)
clause).
--
Duane Hookom
MS Access MVP

Thank you John,

Michel Walsh already posted a not to the same effect, but thank you
anyway.
As I responded already to him, I mentioned that I was wondering how I got
away with not declaring parameters for so long. In the very same database
(Access 2000) alone, I have half a dozen CT queries selecting from queries
with form fields as parameters. In these queries none of the parameters
are Date/Time, they are numbers. Just to be sure I had forgotten that I
defined parameters, I checked, and I found that the 'Parameters' item of
the Query menuitem was disabled.
But I have other applications in A2K where I use dates.

Ragnar

First thing, with a crosstab query you MUST declare your parameters and
if any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Repeat for the second parameter.

Hello,

I have been scratching my head over this problem so I hope someone can
spot
what is wrong.

The CT query is:

TRANSFORM Val(Nz(Count([qryReviews].[Reviews]))) AS ReviewCount
SELECT [qryReviews].[DateRev], Val(Nz(Count([qryReviews].[Reviews]))) AS
DailyTotal
FROM qryReviews
GROUP BY [qryReviews].[DateRev]
PIVOT [qryReviews].[RevInits];

and the query it is selecting from is:

SELECT [Diagnosis CV].[Accession Number] AS Reviews,
[Diagnosis CV].[Date Reviewed] AS DateRev,
[Diagnosis CV].[Reviewer Initials] AS RevInits
FROM [Diagnosis CV]
WHERE
((([Diagnosis CV].[Date Reviewed])>=Forms!frmDateRange!txtStartDate
And ([Diagnosis CV].[Date Reviewed])<=Forms!frmDateRange!txtEndDate));

With the form frmDateRange open, and containing valid start and end
dates, I
can run the select query with no problems.
However, when I run the CT query I get an error message that MS Jet
database
engine does not recognize Forms!frmDateRange!txtEndDate as a valid field
or
expression!

At first I noticed that the QBE had put square brackets around the items
in
the date spec, like this [Forms]![frmDateRange]![txtEndDate], so I
removed
them, but I still get the error message. I also tried with dots instead
of
bangs, in all cases the bottom level query runs fine but the CT
complains.

I am really puzzled because I have used excactly this construct, with a
CT
query selecting from a subquery where the selection criteria are
applied,
countless times and never had a problem.

Any help would be appreciated.

Ragnar

 

[Michel Walsh]

Hi,


It in only REQUIRED for crosstabs. Other queries do not have that
obligation.

Vanderghast, Access MVP

Thank you Michel, that fixed the problem!

I wonder how I got away with not defining the parameters before?

Ragnar

Hi,


You HAVE TO define the exact data type for each parameter in a
Crosstab query. From the query designer, it is in the menu, under Query |
Parameters. Supply the parameters (FORMS!FormName!ControlName) and the
expected data type.



Hoping it may help,
Vanderghast, Access MVP

Hello,

I have been scratching my head over this problem so I hope someone can
spot
what is wrong.

The CT query is:

TRANSFORM Val(Nz(Count([qryReviews].[Reviews]))) AS ReviewCount
SELECT [qryReviews].[DateRev], Val(Nz(Count([qryReviews].[Reviews]))) AS
DailyTotal
FROM qryReviews
GROUP BY [qryReviews].[DateRev]
PIVOT [qryReviews].[RevInits];

and the query it is selecting from is:

SELECT [Diagnosis CV].[Accession Number] AS Reviews,
[Diagnosis CV].[Date Reviewed] AS DateRev,
[Diagnosis CV].[Reviewer Initials] AS RevInits
FROM [Diagnosis CV]
WHERE
((([Diagnosis CV].[Date Reviewed])>=Forms!frmDateRange!txtStartDate
And ([Diagnosis CV].[Date Reviewed])<=Forms!frmDateRange!txtEndDate));

With the form frmDateRange open, and containing valid start and end
dates, I
can run the select query with no problems.
However, when I run the CT query I get an error message that MS Jet
database
engine does not recognize Forms!frmDateRange!txtEndDate as a valid field
or
expression!

At first I noticed that the QBE had put square brackets around the items
in
the date spec, like this [Forms]![frmDateRange]![txtEndDate], so I
removed
them, but I still get the error message. I also tried with dots instead
of
bangs, in all cases the bottom level query runs fine but the CT
complains.

I am really puzzled because I have used excactly this construct, with a
CT
query selecting from a subquery where the selection criteria are
applied,
countless times and never had a problem.

Any help would be appreciated.

Ragnar