[AccessD] SQL for Subquery being changed

William,

Not seen that before, have you tried changing the select SQL that it puts in
square brackets from:

(SELECT IIR_Parent, Max(Multiply_Pct) AS Max_Fuzzy FROM tbl_Fuzzy_Matches
GROUP BY IIR_Parent)

To

(SELECT [IIR_Parent], Max([Multiply_Pct]) AS Max_Fuzzy FROM
tbl_Fuzzy_Matches
GROUP BY [IIR_Parent])
Just in case its have a mad moment with some of the field names.

Paul
On 21 October 2010 13:26, William Benson (VBACreations.com) <
vbacreations at gmail.com> wrote:

> I have a query which Access allows me to code in SQL, it shows up just fine
> in Design View as well. Somewhere down the road Access changes the SQL.
> Change from regular parentheses to square brackets. Is this normal
> behavior?
>
>
> Also, sometimes the query stops running for no apparent reason. I go into
> edit the SQL, essentially do nothing but remove the final semicolon and all
> of a sudden it is running. Since a subform is based on this query it is
> very
> annoying.
>
> Original:
> SELECT C.Max_Fuzzy, A.Plant_id AS IIR_Plant_ID, B.GIB_Owner_ID
> FROM (tbl_IIR AS A LEFT JOIN qry_103_Matched_Plants_For_Display_All AS B ON
> A.Plant_id=B.Sort_ID) LEFT JOIN
>   (SELECT IIR_Parent, Max(Multiply_Pct) AS Max_Fuzzy FROM tbl_Fuzzy_Matches
> GROUP BY IIR_Parent) AS C
>   ON A.Parentname=C.IIR_Parent
> WHERE (((B.Sort_ID) Is Null));
>
> Access Changes to:
> SELECT C.Max_Fuzzy, A.Plant_id AS IIR_Plant_ID, B.GIB_Owner_ID
> FROM (tbl_IIR AS A LEFT JOIN qry_103_Matched_Plants_For_Display_All AS B ON
> A.Plant_id=B.Sort_ID) LEFT JOIN
>   [SELECT IIR_Parent, Max(Multiply_Pct) AS Max_Fuzzy FROM tbl_Fuzzy_Matches
> GROUP BY IIR_Parent; ] AS C
>   ON A.Parentname=C.IIR_Parent
> WHERE (((B.Sort_ID) Is Null));
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Paul Hartland
paul.hartland at googlemail.com