Crosstab - RecordSource

  • Thread starter Thread starter Craig Hornish
  • Start date Start date
C

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
 
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?
 
Inline comments

Duane Hookom said:
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?
Click to expand...
Yep - should have done that.

Is your
DATE-SHIPPED field text?
Click to expand...
Not mine :) but yes.
Also, your ORDER BY and PIVOT lines seem to be out of order.
Click to expand...
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?
Click to expand...
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
--

Craig Hornish said:
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
Click to expand...
Click to expand...
 
Back
Top