Jet/Query editor destroys query...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark Burns - 30 May 2006 21:10 GMT

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)

Reply to this Message

Jerry Whittle - 30 May 2006 21:26 GMT

What happens when you put double quotes in place of the four single quotes?

What happens when you delete the four * ?

I bet one, the other, or the combination of both is messing up Access.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> 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
[quoted text clipped - 28 lines]
> Windows XP Sp2-level patching for Jet 4.0 - msjet40.dll & msjtes40.dll
> 4.00.8618.0)

Reply to this Message

Mark Burns - 31 May 2006 14:36 GMT

Jerry,

Neither '9999 - ** MUST REASSIGN **' or "9999 - ** MUST REASSIGN **"
seemed to make any difference.
It sure blew up bigger/worse/quicker when it was either
'9999 - ** MUST REASSIGN! **' or "9999 - ** MUST REASSIGN! **" though.

I did not try playing with the *s though.

Interestingly, my original 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) WHERE
(SAP_PLANT_NO=[Forms]![frmIncidentAdd]![Location]);

got instantly mangled, becoming something like:

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 [%&@^ABCD] WHERE
((SAP_PLANT_NO)=Forms!frmIncidentAdd!Location);

Note that the ""s around "999 - ** MUST REASSIGN! **" were somehow ignored
when the parser saw the ! which became ]![ regardless of the fact that it was
within a double-quoted string literal.

I thought the ()s around the UNION sub-query becoming []s and followed by a
. (or at least, it LOOKED like a period) before the "AS ..." was interesting
too. This continued to happen even after I removed the ! from the quotated
literal string.
(I can handle the auto-generated name, I've no fears there, but it is an
_interesting_ auto-naming style though.)

All this happened after _retrieving_ the SQL string from the querydef -
either via the immediate window via
strMySQL = currentdb.querydefs("myQuery").SQL
or by opening the query in the designer (in either SQL or design views).

If I simply saved the query string (by directly assigning the string to the
querydef's .SQL property in the immediate window) and then opened the form
which used it for a combo-box list source, it ran perfectly as expected!

Open the query in the designer, allow it to save the query, and try the form
again... *ka-boom!* Invalid Bracketing.

> What happens when you put double quotes in place of the four single quotes?
>
[quoted text clipped - 34 lines]
> > Windows XP Sp2-level patching for Jet 4.0 - msjet40.dll & msjtes40.dll
> > 4.00.8618.0)

Reply to this Message

Mark Burns - 31 May 2006 15:04 GMT

Oh, just to clarify, the auto-generated name for the sub-query was:
"[%$##@_Alias]"

> Jerry,
>
[quoted text clipped - 81 lines]
> > > Windows XP Sp2-level patching for Jet 4.0 - msjet40.dll & msjtes40.dll
> > > 4.00.8618.0)

Reply to this Message