Crosstab - RecordSource

[Craig Hornish]

Is it possible to assign a crosstab as a recordsource through code?

I have tried using the code below but get the error
"Cannot use the crosstab of a non-Fixed column as a subquery"
When the recordsource is assigned as the named query it works.

Thank you

Craig Hornish



strSQL = "PARAMETERS [Forms]![frmReportControl]![txtYear] Text ( 255 ),
[Forms]![frmReportControl]![cmbReportID] Long; " & _
"TRANSFORM Sum(tmakOrderHistLine.QTY_SHIPPED) AS [Sum Shipped] " & _
"SELECT tblReports.ReportName, 'A' AS HoldPlace, tmakOrderHistLine.CUSTOMER,
tmakOrderHistHead.CUSTOMER_SHIP, tmakOrderHistLine.DATE_SHIPPED,
tmakOrderHistLine.CITY_SHIP, tmakOrderHistLine.STATE_SHIP,
tmakOrderHistHead.CUSTOMER_PO, tmakOrderHistLine.ORDER_NO, 'B' AS Col10 " &
_
"FROM tblReports INNER JOIN (tblReportDetails LEFT JOIN (tmakOrderHistLine
LEFT JOIN tmakOrderHistHead ON (tmakOrderHistLine.ORDER_SUFFIX =
tmakOrderHistHead.ORDER_SUFFIX) AND (tmakOrderHistLine.ORDER_NO =
tmakOrderHistHead.ORDER_NO) AND (tmakOrderHistLine.INVOICE =
tmakOrderHistHead.INVOICE)) ON tblReportDetails.PartID =
tmakOrderHistLine.PART) ON tblReports.ReportID = tblReportDetails.ReportID "
& _
"WHERE (((tblReportDetails.ReportID) =
[Forms]![frmReportControl]![cmbReportID]) And
((Left([tmakOrderHistLine]![DATE_SHIPPED], 4)) =
[Forms]![frmReportControl]![txtYear] Or
(Left([tmakOrderHistLine]![DATE_SHIPPED], 4)) Is Null) And
((tmakOrderHistLine.FLAG_BOM) = 'Y' Or (tmakOrderHistLine.FLAG_BOM) = 'N' Or
(tmakOrderHistLine.FLAG_BOM) = ' ' Or (tmakOrderHistLine.FLAG_BOM) Is Null)
And ((tmakOrderHistLine.ITEM_SORTKEY) <> 'EXCEPTION' Or
(tmakOrderHistLine.ITEM_SORTKEY) Is Null)) " & _
"GROUP BY tblReports.ReportName, 'A', tmakOrderHistLine.CUSTOMER,
tmakOrderHistHead.CUSTOMER_SHIP, tmakOrderHistLine.DATE_SHIPPED,
tmakOrderHistLine.CITY_SHIP, tmakOrderHistLine.STATE_SHIP,
tmakOrderHistHead.CUSTOMER_PO, tmakOrderHistLine.ORDER_NO, 'B' " & _
"PIVOT tblReportDetails.Part;"
'"ORDER BY tmakOrderHistLine.CUSTOMER, tmakOrderHistHead.CUSTOMER_SHIP " & _

Me.RecordSource = strSQL

 

[Duane Hookom]

If you have the entire sql to play with, why do you use two parameters when
you could substitute the actual values in the where clause? Is your
DATE-SHIPPED field text?

Also, your ORDER BY and PIVOT lines seem to be out of order. Are we also to
assume that you can't provide the Column Headings in the sql?

 

[Craig Hornish]

Inline comments

If you have the entire sql to play with, why do you use two parameters
when you could substitute the actual values in the where clause?

Yep - should have done that.

Is your

DATE-SHIPPED field text?

Not mine but yes.

Also, your ORDER BY and PIVOT lines seem to be out of order.

Yeah I commented that out because of the "error explanation", and my
attempt to correct it.

Are we also to

assume that you can't provide the Column Headings in the sql?

Not sure exaclty what you mean but the "Part"s could be anything.

I'll try to get rid of the Parameters and see what happen's.
Thanks for looking at this.

Craig Hornish

--
Duane Hookom
MS Access MVP
--

Is it possible to assign a crosstab as a recordsource through code?

I have tried using the code below but get the error
"Cannot use the crosstab of a non-Fixed column as a subquery"
When the recordsource is assigned as the named query it works.

Thank you

Craig Hornish



strSQL = "PARAMETERS [Forms]![frmReportControl]![txtYear] Text ( 255 ),
[Forms]![frmReportControl]![cmbReportID] Long; " & _
"TRANSFORM Sum(tmakOrderHistLine.QTY_SHIPPED) AS [Sum Shipped] " & _
"SELECT tblReports.ReportName, 'A' AS HoldPlace,
tmakOrderHistLine.CUSTOMER, tmakOrderHistHead.CUSTOMER_SHIP,
tmakOrderHistLine.DATE_SHIPPED, tmakOrderHistLine.CITY_SHIP,
tmakOrderHistLine.STATE_SHIP, tmakOrderHistHead.CUSTOMER_PO,
tmakOrderHistLine.ORDER_NO, 'B' AS Col10 " & _
"FROM tblReports INNER JOIN (tblReportDetails LEFT JOIN
(tmakOrderHistLine LEFT JOIN tmakOrderHistHead ON
(tmakOrderHistLine.ORDER_SUFFIX = tmakOrderHistHead.ORDER_SUFFIX) AND
(tmakOrderHistLine.ORDER_NO = tmakOrderHistHead.ORDER_NO) AND
(tmakOrderHistLine.INVOICE = tmakOrderHistHead.INVOICE)) ON
tblReportDetails.PartID = tmakOrderHistLine.PART) ON tblReports.ReportID
= tblReportDetails.ReportID " & _
"WHERE (((tblReportDetails.ReportID) =
[Forms]![frmReportControl]![cmbReportID]) And
((Left([tmakOrderHistLine]![DATE_SHIPPED], 4)) =
[Forms]![frmReportControl]![txtYear] Or
(Left([tmakOrderHistLine]![DATE_SHIPPED], 4)) Is Null) And
((tmakOrderHistLine.FLAG_BOM) = 'Y' Or (tmakOrderHistLine.FLAG_BOM) = 'N'
Or (tmakOrderHistLine.FLAG_BOM) = ' ' Or (tmakOrderHistLine.FLAG_BOM) Is
Null) And ((tmakOrderHistLine.ITEM_SORTKEY) <> 'EXCEPTION' Or
(tmakOrderHistLine.ITEM_SORTKEY) Is Null)) " & _
"GROUP BY tblReports.ReportName, 'A', tmakOrderHistLine.CUSTOMER,
tmakOrderHistHead.CUSTOMER_SHIP, tmakOrderHistLine.DATE_SHIPPED,
tmakOrderHistLine.CITY_SHIP, tmakOrderHistLine.STATE_SHIP,
tmakOrderHistHead.CUSTOMER_PO, tmakOrderHistLine.ORDER_NO, 'B' " & _
"PIVOT tblReportDetails.Part;"
'"ORDER BY tmakOrderHistLine.CUSTOMER, tmakOrderHistHead.CUSTOMER_SHIP "
& _

Me.RecordSource = strSQL