Jet error running crosstab query based on select query

[Guest]

I have a macro that calls a crosstab query, that is based on a select query.
The select query references a form for data input.

When I click the form's command button to run the macro, I get this error:
The Microsoft Jet Database does not recognize Forms!frmMainKPI!txtSOStartDate
as a valid field name or expression. This field happens to be one of two
dates from the form driving the Select query.

The SQL for the select query is:
SELECT tblConsole.Buyer AS con_buyer, tblConsole.ShippingType AS
con_ShippingType, qsZoneReqFinalUnique.PurchDoc AS zr_PurchDoc,
qsZoneReqFinalUnique.Docdate AS zr_Docdate, qsZoneReqFinalUnique.Material AS
zr_Material, qsZoneReqFinalUnique.Shorttext AS zr_Shorttext,
qsZoneReqFinalUnique.POrg AS zr_POrg, qsZoneReqFinalUnique.Plnt AS zr_Plnt,
qsSalesOrderFinalUnique.SDDoc AS so_SDDoc,
qsSalesOrderFinalUnique.so_DocDate, qsSalesOrderFinalUnique.Status AS
so_Status, qsBOFinalUnique.PurchDoc AS bo_PurchDoc, qsBOFinalUnique.Docdate
AS bo_Docdate, DateDiff("d",[so_docdate],qsbofinalunique.docdate) AS
kpi_ElapsedTime_So_to_BO, qsGoodsReceiptUnique.DocDate AS pgr_docdate,
qsGoodsReceiptUnique.Headertxt AS pgr_Headertxt,
DateDiff("d",qsbofinalunique.docdate,qsgoodsreceiptunique.docdate) AS
kpi_ElapsedTime_Bo_to_PGR, qsGoodsReceiptUnique_1.DocDate AS zgr_DocDate,
qsGoodsReceiptUnique_1.Headertxt AS zgr_Headertxt,
qsGoodsReceiptUnique_1.MvtTypeText AS zgr_MvtTypeText,
DateDiff("d",qsgoodsreceiptunique.docdate,qsgoodsreceiptunique_1.docdate) AS
kpi_ElapsedTime_Pgr_to_ZGR, IIf((qsgoodsreceiptunique_1.docdate Is Not Null)
And (qsgoodsreceiptunique.docdate Is Not Null),1,0) AS
kpi_ElapsedTime_Pgr_to_ZGR_cnt
FROM ((((qsZoneReqFinalUnique LEFT JOIN qsSalesOrderFinalUnique ON
(qsZoneReqFinalUnique.PurchDoc = qsSalesOrderFinalUnique.PONumber) AND
(qsZoneReqFinalUnique.Material = qsSalesOrderFinalUnique.Material)) LEFT JOIN
qsBOFinalUnique ON (qsSalesOrderFinalUnique.Material =
qsBOFinalUnique.Material) AND (qsSalesOrderFinalUnique.SDDoc =
qsBOFinalUnique.TrackingNo)) LEFT JOIN tblConsole ON
qsSalesOrderFinalUnique.SDDoc = tblConsole.SalesOrd) LEFT JOIN
qsGoodsReceiptUnique ON (qsBOFinalUnique.PurchDoc = qsGoodsReceiptUnique.PO)
AND (qsBOFinalUnique.Material = qsGoodsReceiptUnique.Material)) LEFT JOIN
qsGoodsReceiptUnique AS qsGoodsReceiptUnique_1 ON
(qsZoneReqFinalUnique.Material = qsGoodsReceiptUnique_1.Material) AND
(qsZoneReqFinalUnique.PurchDoc = qsGoodsReceiptUnique_1.PO)
WHERE (((qsSalesOrderFinalUnique.so_DocDate) Between
[Forms]![frmMainKPI]![txtSOStartDate] And
[Forms]![frmMainKPI]![txtSOEndDate]));

The SQL for the crosstab query:
TRANSFORM Sum([kpi_elapsedtime_so_to_bo])/Count([kpi_elapsedtime_so_to_bo])
AS kpi_ElapsedTime_so_to_bo_days
SELECT qsKpiLeadTimes.zr_Plnt
FROM qsKpiLeadTimes
WHERE (((qsKpiLeadTimes.bo_Docdate) Is Not Null))
GROUP BY qsKpiLeadTimes.zr_Plnt
PIVOT qsKpiLeadTimes.con_ShippingType;

Anyone have any ideas?
thanks.
Steve.

 

[Allen Browne]

Try explicitly declaring the parameter in the first query.

Choose Parameters on the Query menu, and enter 2 rows in the dialog:
[Forms]![frmMainKPI]![txtSOStartDate] Date/Time
[Forms]![frmMainKPI]![txtSOEndDate])) Date/Time

Alternatively (or additionally), you could try listing all the possible
values for the Column Headings property in the crosstab query (in the
Properties box in query design.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a macro that calls a crosstab query, that is based on a select
query.
The select query references a form for data input.

When I click the form's command button to run the macro, I get this error:
The Microsoft Jet Database does not recognize
Forms!frmMainKPI!txtSOStartDate
as a valid field name or expression. This field happens to be one of two
dates from the form driving the Select query.

The SQL for the select query is:
SELECT tblConsole.Buyer AS con_buyer, tblConsole.ShippingType AS
con_ShippingType, qsZoneReqFinalUnique.PurchDoc AS zr_PurchDoc,
qsZoneReqFinalUnique.Docdate AS zr_Docdate, qsZoneReqFinalUnique.Material
AS
zr_Material, qsZoneReqFinalUnique.Shorttext AS zr_Shorttext,
qsZoneReqFinalUnique.POrg AS zr_POrg, qsZoneReqFinalUnique.Plnt AS
zr_Plnt,
qsSalesOrderFinalUnique.SDDoc AS so_SDDoc,
qsSalesOrderFinalUnique.so_DocDate, qsSalesOrderFinalUnique.Status AS
so_Status, qsBOFinalUnique.PurchDoc AS bo_PurchDoc,
qsBOFinalUnique.Docdate
AS bo_Docdate, DateDiff("d",[so_docdate],qsbofinalunique.docdate) AS
kpi_ElapsedTime_So_to_BO, qsGoodsReceiptUnique.DocDate AS pgr_docdate,
qsGoodsReceiptUnique.Headertxt AS pgr_Headertxt,
DateDiff("d",qsbofinalunique.docdate,qsgoodsreceiptunique.docdate) AS
kpi_ElapsedTime_Bo_to_PGR, qsGoodsReceiptUnique_1.DocDate AS zgr_DocDate,
qsGoodsReceiptUnique_1.Headertxt AS zgr_Headertxt,
qsGoodsReceiptUnique_1.MvtTypeText AS zgr_MvtTypeText,
DateDiff("d",qsgoodsreceiptunique.docdate,qsgoodsreceiptunique_1.docdate)
AS
kpi_ElapsedTime_Pgr_to_ZGR, IIf((qsgoodsreceiptunique_1.docdate Is Not
Null)
And (qsgoodsreceiptunique.docdate Is Not Null),1,0) AS
kpi_ElapsedTime_Pgr_to_ZGR_cnt
FROM ((((qsZoneReqFinalUnique LEFT JOIN qsSalesOrderFinalUnique ON
(qsZoneReqFinalUnique.PurchDoc = qsSalesOrderFinalUnique.PONumber) AND
(qsZoneReqFinalUnique.Material = qsSalesOrderFinalUnique.Material)) LEFT
JOIN
qsBOFinalUnique ON (qsSalesOrderFinalUnique.Material =
qsBOFinalUnique.Material) AND (qsSalesOrderFinalUnique.SDDoc =
qsBOFinalUnique.TrackingNo)) LEFT JOIN tblConsole ON
qsSalesOrderFinalUnique.SDDoc = tblConsole.SalesOrd) LEFT JOIN
qsGoodsReceiptUnique ON (qsBOFinalUnique.PurchDoc =
qsGoodsReceiptUnique.PO)
AND (qsBOFinalUnique.Material = qsGoodsReceiptUnique.Material)) LEFT JOIN
qsGoodsReceiptUnique AS qsGoodsReceiptUnique_1 ON
(qsZoneReqFinalUnique.Material = qsGoodsReceiptUnique_1.Material) AND
(qsZoneReqFinalUnique.PurchDoc = qsGoodsReceiptUnique_1.PO)
WHERE (((qsSalesOrderFinalUnique.so_DocDate) Between
[Forms]![frmMainKPI]![txtSOStartDate] And
[Forms]![frmMainKPI]![txtSOEndDate]));

The SQL for the crosstab query:
TRANSFORM
Sum([kpi_elapsedtime_so_to_bo])/Count([kpi_elapsedtime_so_to_bo])
AS kpi_ElapsedTime_so_to_bo_days
SELECT qsKpiLeadTimes.zr_Plnt
FROM qsKpiLeadTimes
WHERE (((qsKpiLeadTimes.bo_Docdate) Is Not Null))
GROUP BY qsKpiLeadTimes.zr_Plnt
PIVOT qsKpiLeadTimes.con_ShippingType;

Anyone have any ideas?
thanks.
Steve.