return all records in crosstab query

[Guest]

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;

 

[Guest]

http://www.mvps.org/access/queries/qry0001.htm

 

[Guest]

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

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;

 

[Guest]

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

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

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;

 

[Guest]

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];

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

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

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;