Acc2003: Problem with UNION query in subreport?

[planetthoughtful]

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

[planetthoughtful]

Okay, it appears it is a limitation of Acc2003, in that it doesn't seem
to like UNION queries embedded in a subquery. A little odd - most other
db environments would cope with this, but now that I've removed the
subquery component and I'm providing ordering via the subreport, all
seems to work okay.

Much warmth,

planetthoughtful
---
"Lost in thought"
http://www.planetthoughtful.org

[david epsom dot com dot au]

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" <pl**************@gmail.com> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...

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