Yes, as others have pointed out before me :~), Access will
tend to mangle "(select ...)" into "[select ...]."
(Note the full stop after the final square bracket)
[]. was the Jet 3.5 format, but never properly supported.
() is the Jet 4.0 format, but Access will rewrite it
if given a chance.
For very simple sub-selects, I think that this might not
matter. For more complex sub-selects, you need to write
the sub-select as a stored query. For dynamic SQL, where
Access doesn't have a chance to re-write the SQL, you
can use anything that works.
(david)
"planetthoughtful" <planetthoughtful@gmail.com> wrote in message
news:1142129931.284872.50080@e56g2000cwe.googlegro ups.com...[color=blue]
> Hi All,
>
> I'd like to place a subreport onto a report that draws its records from
> a UNION query.
>
> When I design the query and view the records all looks fine. However,
> when I put the query as the subreport's data source and open it, Access
> complains that Jet can't find the table, and then when I open up the
> query itself to look at the SQL, it appears all mangled.
>
> The query def is as follows:
>
> SELECT recdate, orderid, outcontext, outtext, reviewdate, recstatus
> FROM (SELECT recdate, orderid, iif(context=1, "Call", iif(context=2,
> "Email", iif(context=3,"Check Order",iif(context=4,"Speak to","Find
> out")))) as outcontext, nextaction as outtext, reviewdt, IIF(status=1,
> "Outstanding", "Resolved") as recstatus FROM tbl_ordernextactions
> UNION
> SELECT recdate, orderid,iif(notetype=1, "Outstanding", "Resolved") as
> outcontext, note as outtext, "" as reviewdate, "" as recstatus FROM
> tbl_ordernotes) AS unquery order by recdate
>
> When I try and attach it to the subreport and open it, it gets mangled
> to:
>
> SELECT recdate, orderid, outcontext, outtext, reviewdt, recstatus
> FROM [SELECT recdate, orderid, iif(context=1, "Call", iif(context=2,
> "Email", iif(context=3,"Check Order",iif(context=4,"Speak to","Find
> out")))) as outcontext, nextaction as outtext, reviewdt, IIF(status=1,
> "Outstanding", "Resolved") as recstatus FROM tbl_o] AS unquery
> ORDER BY recdate;
>
> Is this a bug, or a limitation of Acc2003, or am I doing something
> wrong in my query def?
>
> Any help appreciated!!
>
> Much warmth,
>
> planetthoughtful
> ---
> "Lost in thought"
>
http://www.planetthoughtful.org
>[/color]
Acc2003: Problem with UNION query in subreport? 
