return all records in crosstab query

A

Alex

I have a combo box on user pick form where users can choose one Operator and
leave the box blank to return all records. How do I revise my below code to
allow users to leave the [frm_Pick_Operation_Emp_Chart]![Operationcmb] field
on my pick form blank? Thank you!

PARAMETERS [forms]![frm_Pick_Operation_Emp_Chart]![BegDatetxt] DateTime,
[forms]![frm_Pick_Operation_Emp_Chart]![EndDatetxt] DateTime,
[forms]![frm_Pick_Operation_Emp_Chart]![Operationcmb] Text ( 255 );
TRANSFORM Sum(qry_Defect_by_Operation_Emp_Chart.[SumOfDef Qnty]) AS
[SumOfSumOfDef Qnty]
SELECT qry_Defect_by_Operation_Emp_Chart.Operation
FROM qry_Defect_by_Operation_Emp_Chart
GROUP BY qry_Defect_by_Operation_Emp_Chart.Operation
PIVOT qry_Defect_by_Operation_Emp_Chart.Oper;
 
A

Alex

Yes, but how do you specify that in the Query parameters in a Crosstab query?

Mike C said:
http://www.mvps.org/access/queries/qry0001.htm



Alex said:
I have a combo box on user pick form where users can choose one Operator and
leave the box blank to return all records. How do I revise my below code to
allow users to leave the [frm_Pick_Operation_Emp_Chart]![Operationcmb] field
on my pick form blank? Thank you!

PARAMETERS [forms]![frm_Pick_Operation_Emp_Chart]![BegDatetxt] DateTime,
[forms]![frm_Pick_Operation_Emp_Chart]![EndDatetxt] DateTime,
[forms]![frm_Pick_Operation_Emp_Chart]![Operationcmb] Text ( 255 );
TRANSFORM Sum(qry_Defect_by_Operation_Emp_Chart.[SumOfDef Qnty]) AS
[SumOfSumOfDef Qnty]
SELECT qry_Defect_by_Operation_Emp_Chart.Operation
FROM qry_Defect_by_Operation_Emp_Chart
GROUP BY qry_Defect_by_Operation_Emp_Chart.Operation
PIVOT qry_Defect_by_Operation_Emp_Chart.Oper;
 
M

Mike C

I believe you can leave the parametors as is and just modify the criteria in
the QBE for each field.

For example ,in the BegDatetxt criteria field:
[forms]![frm_Pick_Operation_Emp_Chart]![BegDatetxt] or
[forms]![frm_Pick_Operation_Emp_Chart]![BegDatetxt] is Null



Alex said:
Yes, but how do you specify that in the Query parameters in a Crosstab query?

Mike C said:
http://www.mvps.org/access/queries/qry0001.htm



Alex said:
I have a combo box on user pick form where users can choose one Operator and
leave the box blank to return all records. How do I revise my below code to
allow users to leave the [frm_Pick_Operation_Emp_Chart]![Operationcmb] field
on my pick form blank? Thank you!

PARAMETERS [forms]![frm_Pick_Operation_Emp_Chart]![BegDatetxt] DateTime,
[forms]![frm_Pick_Operation_Emp_Chart]![EndDatetxt] DateTime,
[forms]![frm_Pick_Operation_Emp_Chart]![Operationcmb] Text ( 255 );
TRANSFORM Sum(qry_Defect_by_Operation_Emp_Chart.[SumOfDef Qnty]) AS
[SumOfSumOfDef Qnty]
SELECT qry_Defect_by_Operation_Emp_Chart.Operation
FROM qry_Defect_by_Operation_Emp_Chart
GROUP BY qry_Defect_by_Operation_Emp_Chart.Operation
PIVOT qry_Defect_by_Operation_Emp_Chart.Oper;
 
A

Alex

No, the parameters must be defined in the Query Parameters - I figured it out:

PARAMETERS [forms]![frm_Pick_Operation_Emp_Chart]![BegDatetxt] DateTime,
[forms]![frm_Pick_Operation_Emp_Chart]![EndDatetxt] DateTime,
[forms]![frm_Pick_Operation_Emp_Chart]![Operationcmb] Text ( 255 );
TRANSFORM Sum([qry_Defect_by_Operation_Emp_Chart].[SumOfDef Qnty]) AS
[SumOfSumOfDef Qnty]
SELECT [qry_Defect_by_Operation_Emp_Chart].[Operation]
FROM qry_Defect_by_Operation_Emp_Chart
WHERE
[qry_Defect_by_Operation_Emp_Chart].[Operation]=Nz([forms]![frm_Pick_Operation_Emp_Chart]![Operationcmb],[qry_Defect_by_Operation_Emp_Chart].[Operation])
GROUP BY [qry_Defect_by_Operation_Emp_Chart].[Operation]
PIVOT [qry_Defect_by_Operation_Emp_Chart].[Oper];


Mike C said:
I believe you can leave the parametors as is and just modify the criteria in
the QBE for each field.

For example ,in the BegDatetxt criteria field:
[forms]![frm_Pick_Operation_Emp_Chart]![BegDatetxt] or
[forms]![frm_Pick_Operation_Emp_Chart]![BegDatetxt] is Null



Alex said:
Yes, but how do you specify that in the Query parameters in a Crosstab query?

Mike C said:
http://www.mvps.org/access/queries/qry0001.htm



:

I have a combo box on user pick form where users can choose one Operator and
leave the box blank to return all records. How do I revise my below code to
allow users to leave the [frm_Pick_Operation_Emp_Chart]![Operationcmb] field
on my pick form blank? Thank you!

PARAMETERS [forms]![frm_Pick_Operation_Emp_Chart]![BegDatetxt] DateTime,
[forms]![frm_Pick_Operation_Emp_Chart]![EndDatetxt] DateTime,
[forms]![frm_Pick_Operation_Emp_Chart]![Operationcmb] Text ( 255 );
TRANSFORM Sum(qry_Defect_by_Operation_Emp_Chart.[SumOfDef Qnty]) AS
[SumOfSumOfDef Qnty]
SELECT qry_Defect_by_Operation_Emp_Chart.Operation
FROM qry_Defect_by_Operation_Emp_Chart
GROUP BY qry_Defect_by_Operation_Emp_Chart.Operation
PIVOT qry_Defect_by_Operation_Emp_Chart.Oper;
 

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

Top