Filter in Crosstab Query (?)

S

Sev

I have a next CrosstabQ, for example to FEB2001.

TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE (((Table1.Day) Between (#2/1/2001#) And
(#2/28/2001#)) AND ((Table1.Param)="A" Or (Table1.Param)
="B"))
GROUP BY Table1.Day
PIVOT Table1.Param;

More one CrosstabQ for each MonthYear.
To prevent to have one query for each MonthYear, I would
like to know, if is possible to apply a filter in this
case and how, type [Choose Month and Year] how in Select
Query.

Many thanks in advance.
Sev
 
D

Duane Hookom

Create a form frmDates with a text box txtDate where the user can enter any
date in the month. Then change your query to:

PARAMETERS Forms!frmDates!txtDate Date/Time;
TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE Year(Table1.Day) = Year(Forms!frmDates!txtDate) AND
Month(Table1.Day)=Month(Forms!frmDates!txtDate) AND
Table1.Param IN ("A","B")
GROUP BY Table1.Day
PIVOT Table1.Param;
 
S

Sev

Thanks for your reply.

In query I obtained next error.
"Sintax error in PARAMETER clause"
(?)
Thanks
Sev
-----Original Message-----
Create a form frmDates with a text box txtDate where the user can enter any
date in the month. Then change your query to:

PARAMETERS Forms!frmDates!txtDate Date/Time;
TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE Year(Table1.Day) = Year(Forms!frmDates!txtDate) AND
Month(Table1.Day)=Month(Forms!frmDates!txtDate) AND
Table1.Param IN ("A","B")
GROUP BY Table1.Day
PIVOT Table1.Param;



--
Duane Hookom
MS Access MVP


I have a next CrosstabQ, for example to FEB2001.

TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE (((Table1.Day) Between (#2/1/2001#) And
(#2/28/2001#)) AND ((Table1.Param)="A" Or (Table1.Param)
="B"))
GROUP BY Table1.Day
PIVOT Table1.Param;

More one CrosstabQ for each MonthYear.
To prevent to have one query for each MonthYear, I would
like to know, if is possible to apply a filter in this
case and how, type [Choose Month and Year] how in Select
Query.

Many thanks in advance.
Sev
Click to expand...


.
Click to expand...
 
D

Duane Hookom

Post your SQL view.

--
Duane Hookom
MS Access MVP


Sev said:
Thanks for your reply.

In query I obtained next error.
"Sintax error in PARAMETER clause"
(?)
Thanks
Sev
-----Original Message-----
Create a form frmDates with a text box txtDate where the user can enter any
date in the month. Then change your query to:

PARAMETERS Forms!frmDates!txtDate Date/Time;
TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE Year(Table1.Day) = Year(Forms!frmDates!txtDate) AND
Month(Table1.Day)=Month(Forms!frmDates!txtDate) AND
Table1.Param IN ("A","B")
GROUP BY Table1.Day
PIVOT Table1.Param;



--
Duane Hookom
MS Access MVP


I have a next CrosstabQ, for example to FEB2001.

TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE (((Table1.Day) Between (#2/1/2001#) And
(#2/28/2001#)) AND ((Table1.Param)="A" Or (Table1.Param)
="B"))
GROUP BY Table1.Day
PIVOT Table1.Param;

More one CrosstabQ for each MonthYear.
To prevent to have one query for each MonthYear, I would
like to know, if is possible to apply a filter in this
case and how, type [Choose Month and Year] how in Select
Query.

Many thanks in advance.
Sev
Click to expand...


.
Click to expand...
Click to expand...
 
D

Duane Hookom

Try replace Date/Time with DateTime

--
Duane Hookom
MS Access MVP


Sev said:
Thanks for your reply.

In query I obtained next error.
"Sintax error in PARAMETER clause"
(?)
Thanks
Sev
-----Original Message-----
Create a form frmDates with a text box txtDate where the user can enter any
date in the month. Then change your query to:

PARAMETERS Forms!frmDates!txtDate Date/Time;
TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE Year(Table1.Day) = Year(Forms!frmDates!txtDate) AND
Month(Table1.Day)=Month(Forms!frmDates!txtDate) AND
Table1.Param IN ("A","B")
GROUP BY Table1.Day
PIVOT Table1.Param;



--
Duane Hookom
MS Access MVP


I have a next CrosstabQ, for example to FEB2001.

TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE (((Table1.Day) Between (#2/1/2001#) And
(#2/28/2001#)) AND ((Table1.Param)="A" Or (Table1.Param)
="B"))
GROUP BY Table1.Day
PIVOT Table1.Param;

More one CrosstabQ for each MonthYear.
To prevent to have one query for each MonthYear, I would
like to know, if is possible to apply a filter in this
case and how, type [Choose Month and Year] how in Select
Query.

Many thanks in advance.
Sev
Click to expand...


.
Click to expand...
Click to expand...
 
S

Sev

Ok!
Thanks. Canged to DateTime don't show any error.
But now I don't understend as to relate the Text box, in
Form, with Query to obtain results in query(?)

More one time, thanks.
Sev

-----Original Message-----
Try replace Date/Time with DateTime

--
Duane Hookom
MS Access MVP


Thanks for your reply.

In query I obtained next error.
"Sintax error in PARAMETER clause"
(?)
Thanks
Sev
-----Original Message-----
Create a form frmDates with a text box txtDate where
Click to expand...
the
user can enter any
date in the month. Then change your query to:

PARAMETERS Forms!frmDates!txtDate Date/Time;
TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE Year(Table1.Day) = Year(Forms!frmDates!txtDate) AND
Month(Table1.Day)=Month(Forms!frmDates!txtDate) AND
Table1.Param IN ("A","B")
GROUP BY Table1.Day
PIVOT Table1.Param;



--
Duane Hookom
MS Access MVP


I have a next CrosstabQ, for example to FEB2001.

TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE (((Table1.Day) Between (#2/1/2001#) And
(#2/28/2001#)) AND ((Table1.Param)="A" Or (Table1.Param)
="B"))
GROUP BY Table1.Day
PIVOT Table1.Param;

More one CrosstabQ for each MonthYear.
To prevent to have one query for each MonthYear, I would
like to know, if is possible to apply a filter in this
case and how, type [Choose Month and Year] how in Select
Query.

Many thanks in advance.
Sev



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Duane Hookom

Open the form and enter any date. The query will display the results for the
month of the date entered.

--
Duane Hookom
MS Access MVP


Sev said:
Ok!
Thanks. Canged to DateTime don't show any error.
But now I don't understend as to relate the Text box, in
Form, with Query to obtain results in query(?)

More one time, thanks.
Sev

-----Original Message-----
Try replace Date/Time with DateTime

--
Duane Hookom
MS Access MVP


Thanks for your reply.

In query I obtained next error.
"Sintax error in PARAMETER clause"
(?)
Thanks
Sev

-----Original Message-----
Create a form frmDates with a text box txtDate where the
user can enter any
date in the month. Then change your query to:

PARAMETERS Forms!frmDates!txtDate Date/Time;
TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE Year(Table1.Day) = Year(Forms!frmDates!txtDate) AND
Month(Table1.Day)=Month(Forms!frmDates!txtDate) AND
Table1.Param IN ("A","B")
GROUP BY Table1.Day
PIVOT Table1.Param;



--
Duane Hookom
MS Access MVP


message
I have a next CrosstabQ, for example to FEB2001.

TRANSFORM Sum(Table1.Valor) AS SumOfValor
SELECT Table1.Day
FROM Table1
WHERE (((Table1.Day) Between (#2/1/2001#) And
(#2/28/2001#)) AND ((Table1.Param)="A" Or (Table1.Param)
="B"))
GROUP BY Table1.Day
PIVOT Table1.Param;

More one CrosstabQ for each MonthYear.
To prevent to have one query for each MonthYear, I would
like to know, if is possible to apply a filter in this
case and how, type [Choose Month and Year] how in Select
Query.

Many thanks in advance.
Sev



.
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

Crosstab query with unique count 6
Return all row headings in crosstab query 3
crosstab query fails when based on query with subquery? 6
IIF or something else 3
Simple query 2
UnMatched Query 2
Urgent - Create column on query depending on another column text 1
add end dates to query by start date 4

Top