G
Guest
Ths following query *actually runs OK* - until Access opens it in the Query
editor (opening it in either the Design or SQL views will break it with
subsequent attempts returning an "Invalid Bracketing" SQL syntax error).
SQL: SELECT KOSTL, NAME, SAP_PLANT_NO FROM (SELECT SAP_PLANT_NO, KOSTL,
[KOSTL] & " - " & [KTEXT] AS NAME FROM tblPlantCostCenters UNION SELECT
DISTINCT SAP_PLANT_NO, '9999' AS KOSTL, '9999 - ** MUST REASSIGN **' AS NAME
FROM tblPlantCostCenters) AS CostCenterUNIONSubQuery_Alias WHERE
(SAP_PLANT_NO=[Forms]![frmIncidentAdd]![Location]);
....and if you change the literal '9999 - ** MUST REASSIGN **' to '9999 - **
MUST REASSIGN! **' things go REALLY haywire quick!
Now, First off, I do know that I can extract the sub-query to a separate
query like this:
1) qryCostCenterUNIONSubQuery: SELECT SAP_PLANT_NO, KOSTL, [KOSTL] & " - " &
[KTEXT] AS NAME FROM tblPlantCostCenters UNION SELECT DISTINCT SAP_PLANT_NO,
'9999' AS KOSTL, '9999 - ** MUST REASSIGN **' AS NAME FROM
tblPlantCostCenters;
2) MainComboBoxQuery: SELECT KOSTL, NAME, SAP_PLANT_NO
FROM qryCostCenterUNIONSubQuery
WHERE (SAP_PLANT_NO=Forms!frmIncidentAdd!Location);
....and it all works just great (even WITH the
overly-troublesome-in-the-sub-query-extra-!-character).
....but that still leaves the question begging: Why does the query editor
improperly mung-up the (sub-query) SQL to begin with? (Access 2003 Sp2 w/
Windows XP Sp2-level patching for Jet 4.0 - msjet40.dll & msjtes40.dll
4.00.8618.0)
editor (opening it in either the Design or SQL views will break it with
subsequent attempts returning an "Invalid Bracketing" SQL syntax error).
SQL: SELECT KOSTL, NAME, SAP_PLANT_NO FROM (SELECT SAP_PLANT_NO, KOSTL,
[KOSTL] & " - " & [KTEXT] AS NAME FROM tblPlantCostCenters UNION SELECT
DISTINCT SAP_PLANT_NO, '9999' AS KOSTL, '9999 - ** MUST REASSIGN **' AS NAME
FROM tblPlantCostCenters) AS CostCenterUNIONSubQuery_Alias WHERE
(SAP_PLANT_NO=[Forms]![frmIncidentAdd]![Location]);
....and if you change the literal '9999 - ** MUST REASSIGN **' to '9999 - **
MUST REASSIGN! **' things go REALLY haywire quick!
Now, First off, I do know that I can extract the sub-query to a separate
query like this:
1) qryCostCenterUNIONSubQuery: SELECT SAP_PLANT_NO, KOSTL, [KOSTL] & " - " &
[KTEXT] AS NAME FROM tblPlantCostCenters UNION SELECT DISTINCT SAP_PLANT_NO,
'9999' AS KOSTL, '9999 - ** MUST REASSIGN **' AS NAME FROM
tblPlantCostCenters;
2) MainComboBoxQuery: SELECT KOSTL, NAME, SAP_PLANT_NO
FROM qryCostCenterUNIONSubQuery
WHERE (SAP_PLANT_NO=Forms!frmIncidentAdd!Location);
....and it all works just great (even WITH the
overly-troublesome-in-the-sub-query-extra-!-character).
....but that still leaves the question begging: Why does the query editor
improperly mung-up the (sub-query) SQL to begin with? (Access 2003 Sp2 w/
Windows XP Sp2-level patching for Jet 4.0 - msjet40.dll & msjtes40.dll
4.00.8618.0)