473,244 Members | 1,293 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,244 software developers and data experts.

Acc2003: Problem with UNION query in subreport?

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

Mar 12 '06 #1
2 1919
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

Mar 12 '06 #2
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

Mar 13 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Deano | last post by:
OK, I've been taking the detour from hell trying to sort this and after taking a walk I think I need a rethink. I have a report for a single employee that includes a subreport which lists...
1
by: Ellen Manning | last post by:
I've got a report with a subreport linked on fund number. The subreport returns 2 columns of dollar amounts, both current and YTD, by object code for the selected fund number. The user can select...
1
by: Melissa | last post by:
Can anyone help me with this --- I have a grouped report with a subreport in the group footer and the report starts a new page for each group. The subreport is based on a query which has...
3
by: lorirobn | last post by:
Hello, I have a report which uses a subreport. When I run the report, I get "Enter Parameter Value" error message for "tblGuestRoom". I click ok and the report seems to work fine. I...
1
by: Tim Jones | last post by:
Hey all, I am trying to try get a crytal report running properly. A "Hello World" report that does not add criteria to the record selection formula of the report (either in the development tool,...
3
by: russellhq | last post by:
Hi, I'm fairly new to access and have a little trouble with a crosstab query I've setup. I have a main form where the user selects a project name and below in a subform, a crosstab query is...
5
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big...
10
by: MLH | last post by:
Gentlemen: I am having one heck of a time taking a DAO walk through the records in an SQL dynaset. I'm trying to walk a set of records returned by a UNION query. I'm attempting to filter the...
2
by: bhaumikdv | last post by:
i want to change the report's query dynamically. means i want give input as query and then as per my query report should be set. i want to do same for the report's subreport. subreport;s query also i...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.