Hello all, hope someone can help.
I recently built a report showing the distribution of money in and out
of the company. Simplified version of which looks like:
DECEMBER 2004
TELEPHONE SALES: INTERNET: TOTAL:
£1200 £1000 £2200
I then built a sub-report showing adjustments, eg:
ADJUSTMENTS
TELEPHONE SALES: INTERNET: TOTAL:
Refund#1 £12 £1 £13
Refund#2 £2 £10 £12
Finally I created an overall totals for each column using the DSUM
function. Everything worked great. The sub-report is based on a UNION
query, and I realised that this was removing duplicates records, when
obviously in this context they should remain. I therefore changed the
underlying query to a UNION ALL query. This fixed the query in the
respect that it now returns all of the necessary records, however in
the report fields that reference the query using DSUM, the result
returned is '#Error'. I cannot see why this would be. What have I
done wrong? Or is it a bug? Any guidance greatly appreciated. 4 2081 st******@isopla n.co.uk wrote: the report fields that reference the query using DSUM, the result returned is '#Error'. I cannot see why this would be. What have I done wrong? Or is it a bug? Any guidance greatly appreciated.
Do check if the control name collides with a field name. If so, rename
the control and see whether the #error goes away.
--
Bas Cost Budde, Holland http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
It is definately to do with the UNION ALL command. If I go into the
underlying query and remove the word 'ALL' report works fine, error
goes away. Put 'ALL' back in and the error is back.
As a workaround I have added a new unique field to my underlying query,
so that no duplicates are returned, and I can use UNION rather than
UNION ALL. Seems more complicated than necessary, but it works.
Be interested to hear if anyone else out there has had problems with
this, and how they solved it. st******@isopla n.co.uk wrote: It is definately to do with the UNION ALL command. If I go into the underlying query and remove the word 'ALL' report works fine, error goes away. Put 'ALL' back in and the error is back.
Okay. What if you don't use DSum in the report, but grouping? In a group
footer you can use Sum([fieldname]).
Access does several things behind our backs when creating the datasource
for a report. Let's try several approaches in return :-)
--
Bas Cost Budde, Holland http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Access often misunderstands the data type of calculated fields.
Try typecasting the field in the first SELECT statement of the UNION query,
e.g.:
CCur(Nz(DSum("S omeField", "SomeTable) , 0))
More info:
Calculated fields misinterpreted
at: http://members.iinet.net.au/~allenbrowne/ser-45.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<st******@isopl an.co.uk> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.com.. . It is definately to do with the UNION ALL command. If I go into the underlying query and remove the word 'ALL' report works fine, error goes away. Put 'ALL' back in and the error is back. As a workaround I have added a new unique field to my underlying query, so that no duplicates are returned, and I can use UNION rather than UNION ALL. Seems more complicated than necessary, but it works. Be interested to hear if anyone else out there has had problems with this, and how they solved it. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Matias Silva |
last post by:
Hi Everyone, I wrote a for loop to build several select
statements that are combined with a UNION. When I execute
one of the queries separately, it works, but when I execute
the query with a UNION it returns:
ERROR 2013 (HY000): Lost connection to MySQL server during query
I am able to run additional queries after I get the error, so
the connection is not lost. I have tried running the query
with the union on MySQL Query Browser and...
|
by: _link98 |
last post by:
Problem: getting SQL0181N for queries on nicknames to remote
Union-All-View.
Can't see what I'm doing wrong yet, maybe someone has seen this before.
Environment: UDB ESE 8.1 + FIXPAK 9A, on Solaris 8.1, with 64-bit
instances.
I have a NICKNAME called REMOTE_DW.T_MYTABLE which points to a
"union-all-view".
Several other nicknames exist to regular-tables and regular views in
|
by: ranjeet.gupta |
last post by:
Dear ALL
As we know that when we declare the union then we have the size of the
union which is the size of the highest data type as in the below case
the
size should be 4 (For my case and compiler), and it is,
what I conclude from the below code
union data_type {
|
by: deercreek |
last post by:
I have the following table test3.
RESERVATIONID Site SitePrice
101 RV2 $25.00
101 RV3 $25.00
102 RV4 $25.00
102 RV5 $25.00
104 RV10 $25.00
I created a query with the following dsum
|
by: wkaras |
last post by:
In my compiler, the following code generates an error:
union U
{ int i; double d; };
U u;
int *ip = &u.i;
U *up = static_cast<U *>(ip); // error
I have to change the cast to reinterpret_cast for the code
| |
by: Jeremy |
last post by:
I have a situation where i am trying to run two query's and retrieve
one record from each query, then using Union combine them into one
recordset. The First Query is strait forward and should just return
the matching record however the second query needs to find a random
record and return it. The union is causing me some trouble. It seems
that any method i try to find a random record just causes an error.
Here is an example of a query...
|
by: patjones |
last post by:
Good morning all:
In what seems like an ongoing saga to make the DSum function do what I need it to, I am now having trouble with a user-defined function in my VBA module. Here's the offending line of code:
Me!txtLostDaysNoPaySeasonal = DSum("getNumberOfWeekdays(!, !)", "qryTest", "! = 'BK21312'")
In this, getNumberOfWeekdays is a function that I define elsewhere in the module, as such:
Function getNumberOfWeekdays(dteStartDate...
|
by: joefrost |
last post by:
I have the following SQL query that keeps giving me a runtime error 800a0401when running through an asp page. The query is below:
SELECT (Sum(Abs(DSum("Cath_type","tbl1a_IC")))/10*100) AS CT_Total, (Sum(Abs(DSum("Insertion_site","tbl1a_IC")))/10*100) AS IS_Total, (Sum(Abs(DSum("Skin_prep","tbl1a_IC")))/10*100) AS SP_Total, (Sum(Abs(DSum("PP_eqip","tbl1a_IC")))/10*100) AS PPE_Total, Sum(Abs(DSum("Hand_hy","tbl1a_IC")))/10*100 AS HH_Total,...
|
by: jmoudy77 |
last post by:
I've got some vb code that gives me a "previous operation cancelled" error when I try and open the form as a subform in a tabbed control. When I open the form itself I don't get the error. Does DSum() not agree with the tabbed control?
I'm posting the code below.
Private Sub Form_Load()
ERROR STARTS HERE and continues to the next line if I remove the previous function.
DSumDay = DSum("", "StandsQ")
DSumNight = DSum("", "StandsQ")
|
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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |