RE: Jet/Query editor destroys query...

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

"Mark Burns" wrote:

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.

"Jerry Whittle" wrote:

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Mark Burns" wrote:

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)

.

Relevant Pages

  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: Requery subform
    ... are just changing the SQL? ... remove this function and relevent code because the query always exists. ... Dim strProjectType As String ... Dim strProjectStatus As String ...
    (comp.databases.ms-access)
  • Re: Building SQL statement in VBA. Getting 3131 Runtime Error in FROM clause :@
    ... Number of characters in a cell in the query design grid 1,024 ... Since the SQL parser mentions an error in the FROM clause, ... The only problem I can see is the SQL string is very long. ... Dim qdf As QueryDef ...
    (microsoft.public.access.queries)
  • again and again... query analyzer and wrong SQL statement plan... + outer join?
    ... again we have some SQL statement performance problems... ... we have aproblem with a query which use 1 inner join and 3 left outer ... Each outer join is a sub-query which contain a group by: ... This query takes more then 10 minutes, but if I precalculate each sub-query ...
    (microsoft.public.sqlserver.datawarehouse)
Loading