Crosstab criteria problem

P

Pietro

Hi i have aproblem in the below crosstab query:

TRANSFORM Avg([Cap Monthly Report].[On Time%]) AS [AvgOfOn Time%]
SELECT [Cap Monthly Report].[Cls Date], Avg([Cap Monthly Report].[On Time%])
AS [Total Of On Time%]
FROM [Cap Monthly Report]
GROUP BY [Cap Monthly Report].[Cls Date]
PIVOT [Cap Monthly Report].Language;

I want to put the below criteria in the cls date field,but i get an error
Between[forms]![CAP Report]![fromv] And [forms]![cap report]![tov]
 
D

Duane Hookom

You must declare the data types of parameters/criteria in crosstabs (unless
you specify the Column Headers property). Select Query->Parameters and enter
the exact form!control names and their data types:
[forms]![CAP Report]![fromv] Date/Time?
[forms]![cap report]![tov] Date/Time?
 
D

Douglas J. Steele

In crosstab tables, it's critical to define the data types of the
parameters.

Try:

PARAMETERS [forms]![CAP Report]![fromv] DateTime,
[forms]![cap report]![tov] DateTime;
TRANSFORM Avg([Cap Monthly Report].[On Time%]) AS [AvgOfOn Time%]
SELECT [Cap Monthly Report].[Cls Date], Avg([Cap Monthly Report].[On Time%])
AS [Total Of On Time%]
FROM [Cap Monthly Report]
WHERE [Cls Date] BETWEEN [forms]![CAP Report]![fromv] And [forms]![cap
report]![tov]
GROUP BY [Cap Monthly Report].[Cls Date]
PIVOT [Cap Monthly Report].Language;
 
P

Pietro

Thank you for your quick answer...
I tried the below,when the form is closed i get a dialog that asks me to put
the paramete value,but actually when i put the dates,the query opens normally
and shows all records...

Duane Hookom said:
You must declare the data types of parameters/criteria in crosstabs (unless
you specify the Column Headers property). Select Query->Parameters and enter
the exact form!control names and their data types:
[forms]![CAP Report]![fromv] Date/Time?
[forms]![cap report]![tov] Date/Time?
--
Duane Hookom
Microsoft Access MVP


Pietro said:
Hi i have aproblem in the below crosstab query:

TRANSFORM Avg([Cap Monthly Report].[On Time%]) AS [AvgOfOn Time%]
SELECT [Cap Monthly Report].[Cls Date], Avg([Cap Monthly Report].[On Time%])
AS [Total Of On Time%]
FROM [Cap Monthly Report]
GROUP BY [Cap Monthly Report].[Cls Date]
PIVOT [Cap Monthly Report].Language;

I want to put the below criteria in the cls date field,but i get an error
Between[forms]![CAP Report]![fromv] And [forms]![cap report]![tov]
Click to expand...
Click to expand...
 
P

Pietro

I copied and pasted the below statement but i get an error:The Microsoft
Acces database does not recognize [forms]![CAP Report]![tov] DateTime as a
valid field name or expression...

Douglas J. Steele said:
In crosstab tables, it's critical to define the data types of the
parameters.

Try:

PARAMETERS [forms]![CAP Report]![fromv] DateTime,
[forms]![cap report]![tov] DateTime;
TRANSFORM Avg([Cap Monthly Report].[On Time%]) AS [AvgOfOn Time%]
SELECT [Cap Monthly Report].[Cls Date], Avg([Cap Monthly Report].[On Time%])
AS [Total Of On Time%]
FROM [Cap Monthly Report]
WHERE [Cls Date] BETWEEN [forms]![CAP Report]![fromv] And [forms]![cap
report]![tov]
GROUP BY [Cap Monthly Report].[Cls Date]
PIVOT [Cap Monthly Report].Language;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pietro said:
Hi i have aproblem in the below crosstab query:

TRANSFORM Avg([Cap Monthly Report].[On Time%]) AS [AvgOfOn Time%]
SELECT [Cap Monthly Report].[Cls Date], Avg([Cap Monthly Report].[On
Time%])
AS [Total Of On Time%]
FROM [Cap Monthly Report]
GROUP BY [Cap Monthly Report].[Cls Date]
PIVOT [Cap Monthly Report].Language;

I want to put the below criteria in the cls date field,but i get an error
Between[forms]![CAP Report]![fromv] And [forms]![cap report]![tov]
Click to expand...
Click to expand...
 
D

Duane Hookom

The form must be open if you want to use the values in the criteria of your
query.
--
Duane Hookom
Microsoft Access MVP


Pietro said:
Thank you for your quick answer...
I tried the below,when the form is closed i get a dialog that asks me to put
the paramete value,but actually when i put the dates,the query opens normally
and shows all records...

Duane Hookom said:
You must declare the data types of parameters/criteria in crosstabs (unless
you specify the Column Headers property). Select Query->Parameters and enter
the exact form!control names and their data types:
[forms]![CAP Report]![fromv] Date/Time?
[forms]![cap report]![tov] Date/Time?
--
Duane Hookom
Microsoft Access MVP


Pietro said:
Hi i have aproblem in the below crosstab query:

TRANSFORM Avg([Cap Monthly Report].[On Time%]) AS [AvgOfOn Time%]
SELECT [Cap Monthly Report].[Cls Date], Avg([Cap Monthly Report].[On Time%])
AS [Total Of On Time%]
FROM [Cap Monthly Report]
GROUP BY [Cap Monthly Report].[Cls Date]
PIVOT [Cap Monthly Report].Language;

I want to put the below criteria in the cls date field,but i get an error
Between[forms]![CAP Report]![fromv] And [forms]![cap report]![tov]
Click to expand...
Click to expand...
Click to expand...
 
P

Pietro

When the form is open,the query opens without asking for parameters,but still
it show all records,I need help as I should fix this problem now...

Duane Hookom said:
The form must be open if you want to use the values in the criteria of your
query.
--
Duane Hookom
Microsoft Access MVP


Pietro said:
Thank you for your quick answer...
I tried the below,when the form is closed i get a dialog that asks me to put
the paramete value,but actually when i put the dates,the query opens normally
and shows all records...

Duane Hookom said:
You must declare the data types of parameters/criteria in crosstabs (unless
you specify the Column Headers property). Select Query->Parameters and enter
the exact form!control names and their data types:
[forms]![CAP Report]![fromv] Date/Time?
[forms]![cap report]![tov] Date/Time?
--
Duane Hookom
Microsoft Access MVP


:

Hi i have aproblem in the below crosstab query:

TRANSFORM Avg([Cap Monthly Report].[On Time%]) AS [AvgOfOn Time%]
SELECT [Cap Monthly Report].[Cls Date], Avg([Cap Monthly Report].[On Time%])
AS [Total Of On Time%]
FROM [Cap Monthly Report]
GROUP BY [Cap Monthly Report].[Cls Date]
PIVOT [Cap Monthly Report].Language;

I want to put the below criteria in the cls date field,but i get an error
Between[forms]![CAP Report]![fromv] And [forms]![cap report]![tov]
Click to expand...
Click to expand...
Click to expand...
 
F

fredg

Thank you for your quick answer...
I tried the below,when the form is closed i get a dialog that asks me to put
the paramete value,but actually when i put the dates,the query opens normally
and shows all records...

Duane Hookom said:
You must declare the data types of parameters/criteria in crosstabs (unless
you specify the Column Headers property). Select Query->Parameters and enter
the exact form!control names and their data types:
[forms]![CAP Report]![fromv] Date/Time?
[forms]![cap report]![tov] Date/Time?
--
Duane Hookom
Microsoft Access MVP

Pietro said:
Hi i have aproblem in the below crosstab query:

TRANSFORM Avg([Cap Monthly Report].[On Time%]) AS [AvgOfOn Time%]
SELECT [Cap Monthly Report].[Cls Date], Avg([Cap Monthly Report].[On Time%])
AS [Total Of On Time%]
FROM [Cap Monthly Report]
GROUP BY [Cap Monthly Report].[Cls Date]
PIVOT [Cap Monthly Report].Language;

I want to put the below criteria in the cls date field,but i get an error
Between[forms]![CAP Report]![fromv] And [forms]![cap report]![tov]
Click to expand...
Click to expand...
Click to expand...

Regarding > "I tried the below,when the form is closed ..."

In addition to setting the Crosstab query parameters in the Parameter
Dialog, the "Cap Report" Form MUST remain open when you run the query,
otherwise how could the query read the data in the form's controls?
 
P

Pietro

Even if the form is open,the query shows all records...

fredg said:
Thank you for your quick answer...
I tried the below,when the form is closed i get a dialog that asks me to put
the paramete value,but actually when i put the dates,the query opens normally
and shows all records...

Duane Hookom said:
You must declare the data types of parameters/criteria in crosstabs (unless
you specify the Column Headers property). Select Query->Parameters and enter
the exact form!control names and their data types:
[forms]![CAP Report]![fromv] Date/Time?
[forms]![cap report]![tov] Date/Time?
--
Duane Hookom
Microsoft Access MVP

:

Hi i have aproblem in the below crosstab query:

TRANSFORM Avg([Cap Monthly Report].[On Time%]) AS [AvgOfOn Time%]
SELECT [Cap Monthly Report].[Cls Date], Avg([Cap Monthly Report].[On Time%])
AS [Total Of On Time%]
FROM [Cap Monthly Report]
GROUP BY [Cap Monthly Report].[Cls Date]
PIVOT [Cap Monthly Report].Language;

I want to put the below criteria in the cls date field,but i get an error
Between[forms]![CAP Report]![fromv] And [forms]![cap report]![tov]
Click to expand...
Click to expand...

Regarding > "I tried the below,when the form is closed ..."

In addition to setting the Crosstab query parameters in the Parameter
Dialog, the "Cap Report" Form MUST remain open when you run the query,
otherwise how could the query read the data in the form's controls?
Click to expand...
 
F

fredg

Even if the form is open,the query shows all records...

fredg said:
Thank you for your quick answer...
I tried the below,when the form is closed i get a dialog that asks me to put
the paramete value,but actually when i put the dates,the query opens normally
and shows all records...

:

You must declare the data types of parameters/criteria in crosstabs (unless
you specify the Column Headers property). Select Query->Parameters and enter
the exact form!control names and their data types:
[forms]![CAP Report]![fromv] Date/Time?
[forms]![cap report]![tov] Date/Time?
--
Duane Hookom
Microsoft Access MVP

:

Hi i have aproblem in the below crosstab query:

TRANSFORM Avg([Cap Monthly Report].[On Time%]) AS [AvgOfOn Time%]
SELECT [Cap Monthly Report].[Cls Date], Avg([Cap Monthly Report].[On Time%])
AS [Total Of On Time%]
FROM [Cap Monthly Report]
GROUP BY [Cap Monthly Report].[Cls Date]
PIVOT [Cap Monthly Report].Language;

I want to put the below criteria in the cls date field,but i get an error
Between[forms]![CAP Report]![fromv] And [forms]![cap report]![tov]
Click to expand...

Regarding > "I tried the below,when the form is closed ..."

In addition to setting the Crosstab query parameters in the Parameter
Dialog, the "Cap Report" Form MUST remain open when you run the query,
otherwise how could the query read the data in the form's controls?
Click to expand...
Click to expand...

Then please post the full Query SQL.
 
D

Duane Hookom

Share your SQL view. I expect you didn't add:
Between[forms]![CAP Report]![fromv] And [forms]![cap report]![tov]
To the criteria of your query.

--
Duane Hookom
Microsoft Access MVP


Pietro said:
When the form is open,the query opens without asking for parameters,but still
it show all records,I need help as I should fix this problem now...

Duane Hookom said:
The form must be open if you want to use the values in the criteria of your
query.
--
Duane Hookom
Microsoft Access MVP


Pietro said:
Thank you for your quick answer...
I tried the below,when the form is closed i get a dialog that asks me to put
the paramete value,but actually when i put the dates,the query opens normally
and shows all records...

:

You must declare the data types of parameters/criteria in crosstabs (unless
you specify the Column Headers property). Select Query->Parameters and enter
the exact form!control names and their data types:
[forms]![CAP Report]![fromv] Date/Time?
[forms]![cap report]![tov] Date/Time?
--
Duane Hookom
Microsoft Access MVP


:

Hi i have aproblem in the below crosstab query:

TRANSFORM Avg([Cap Monthly Report].[On Time%]) AS [AvgOfOn Time%]
SELECT [Cap Monthly Report].[Cls Date], Avg([Cap Monthly Report].[On Time%])
AS [Total Of On Time%]
FROM [Cap Monthly Report]
GROUP BY [Cap Monthly Report].[Cls Date]
PIVOT [Cap Monthly Report].Language;

I want to put the below criteria in the cls date field,but i get an error
Between[forms]![CAP Report]![fromv] And [forms]![cap report]![tov]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Total sum to reflect only displayed data on the report 2
To Sharkbyte and all: Calculate a total values in group level 6
Data Type Mismatch in Criteria Expression 1
Monthly Reports Using Crosstab 3
Crosstab Query Questions 5
Enter month as Jan rather then 1/1/2006 6
Dsum on a report 8
Criteria Question 13

Top