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(cont ext=4,"Speak to","Find
out")))) as outcontext, nextaction as outtext, reviewdt, IIF(status=1,
"Outstandin g", "Resolved") as recstatus FROM tbl_ordernextac tions
UNION
SELECT recdate, orderid,iif(not etype=1, "Outstandin g", "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(cont ext=4,"Speak to","Find
out")))) as outcontext, nextaction as outtext, reviewdt, IIF(status=1,
"Outstandin g", "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,
planetthoughtfu l
---
"Lost in thought" http://www.planetthoughtful.org 2 1946
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,
planetthoughtfu l
---
"Lost in thought" http://www.planetthoughtful.org
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)
"planetthoughtf ul" <pl************ **@gmail.com> wrote in message
news:11******** *************@e 56g2000cwe.goog legroups.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(cont ext=4,"Speak to","Find out")))) as outcontext, nextaction as outtext, reviewdt, IIF(status=1, "Outstandin g", "Resolved") as recstatus FROM tbl_ordernextac tions UNION SELECT recdate, orderid,iif(not etype=1, "Outstandin g", "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(cont ext=4,"Speak to","Find out")))) as outcontext, nextaction as outtext, reviewdt, IIF(status=1, "Outstandin g", "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,
planetthoughtfu l --- "Lost in thought" http://www.planetthoughtful.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
courses taken by that person.
I ask the user for Start and End dates to allow them to see only courses
that fall into that date range. I'm then trying to open the report to show
the current employee with a list of courses that fall within the date...
|
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 one
or many funds.
I create a query for the subreport's recordsource in the open event.
My problem is that if there's more than one fund number, YTD amounts
from the first fund number show up on the 2nd fund numbers' report,
overwriting the...
|
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 criteria in one of the fields. I have a function in a standard module
where the value of the function is part of the criteria. For each group, I
need to run the
function and have the query requery so the subreport prints
the correct records. Where do I...
|
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 narrowed down this error to the Link Master Fields property setting,
when I tried the same scenario with form/subform. It gave me error:
'The Link Master fields property setting has produced this error: The
object doesn't contain the Automation object...
|
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, or in the code) works fine.
However, when I try to add any parameter through the record selection
formula and/or the report parameters method, the report crashes.
I have tried each of the methods:
| |
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 displayed in a datasheet view. The info is dependant on the project picked.
What I tried to do is set the criteria in the the query to only show results that match the project name but I am having no success. If I type a name into the critera then it...
|
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
table,
we try separate this big table into twelve tables and create a view
|
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 records to
those related to vehicle #60 ( = 60 ). If I explicitly
specify 60 in the SQL ==everything works fine. Take a look:
100 PString = "SELECT & " & Chr$(&H22) & Space(1) &
Chr$(&H22) & " & AS Recipient "
120 PString = PString & "FROM...
|
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 want to pass as main report parameter and den dat query should ne set in the subreport.
can any1 show me the way ?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |