Hi. I'm having problems with the following DSum in my report footer. It gives me #Error when I run the report. I hope someone can help me out as I am at my wits end. What I am trying to do is to create a total budget which is revenues - expenses - expenses in-direct. Those controls are in the detail section of the report as groups. Here is the code.
=DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Revenues'")-DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses In-direct'")-DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses'")
6 3663 PEB 1,418
Recognized Expert Top Contributor
Hi,
Try to do this:
=nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Revenues'"))-nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses In-direct'"))-nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses'"))
:)
Hi. I'm having problems with the following DSum in my report footer. It gives me #Error when I run the report. I hope someone can help me out as I am at my wits end. What I am trying to do is to create a total budget which is revenues - expenses - expenses in-direct. Those controls are in the detail section of the report as groups. Here is the code.
=DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Revenues'")-DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses In-direct'")-DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses'")
Thanks for the suggestion but that gives me an #Error message too. Any other ideas would be appreciated!
PEB 1,418
Recognized Expert Top Contributor
Hey
try separetly
nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Revenues'"))
then
nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses In-direct'"))
and
nz(DSum("[Sum of Total Budget]","qrySummaryRe port","[Type] = 'Expenses'"))
and see the results!
What kind of result it gives and where?
:)
Thanks again but I am still getting an #error message. I'm using =Sum(IIf) expressions, which work, but really want to figure out what is wrong with the DSum so any other thoughts would be great appreciated. Is there a rating on this site?
PEB 1,418
Recognized Expert Top Contributor
So,
qrySummaryRepor t is the name of a query or of your report?
If it is name of Report - so error...
If it is a parametred query - so error...
DSUM works only with tables and queries out of the current record source...
Your solution is better than using Dsum..
About the rating you can express your opinion in Community longe dicussions...
Have a nice day...
:)
Ah ha! It is a 5 parameter query. I never knew that about DSum. Thanks ever so much.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ted Mayett |
last post by:
OK. Here is a glitch, sorry if this has been mentioned before.
This is an erratic glitch. I am now up to three other people besides
myself who have been able to see this glitch. It seems it only
happens in IE. And the <hr> causes this thing to happen.
Now, I have ~tried~ to make this glitch, and that is tough to do. I
had did it that one time, and I should have saved the work. But I
didn't save the work :(
|
by: Rolan |
last post by:
I'm using Access 97 and need some assistance in sorting out a proper
DSum expression, or maybe even DCount might be an alternative. I have
tried numerous combinations, but with no apparent success.
The DSum function relates to a subform (sfrmCost) with a text box
(RefCost) which is intended to be the container for a total from a
query (qryItems). The query has three number columns from the table
(tblItems) of which I have an expression...
|
by: Dalan |
last post by:
I seemed to be having problems with structuring the use of NZ with a
DSum expression. Having tried numerous variations of the expression
without success, I'm asking for assistance.
First some background (Access 97) - the DSum expressions are being
used in grand total text boxes on the footer of a subform. And when
viewing the subform in the linked main form the two grand total boxes
display #Error if no entry had been made. Those with...
|
by: Alex |
last post by:
Acc 97
Hi,
I have the following in my query which works well if it is all set to
numbers on table design.
But what I want to do is where it states MC (short for machine name) use the
actual machine name of which there are 5 off. therefore, on the table design
I flick it over to text mode.
|
by: Mark Reed |
last post by:
All,
I have never used this function before and am not sure it what I need.
Just to clarify, I have a report based on a query which has amoungst other
field, wk, parea & packs_req. What I am trying to do is create a subreport
for the main report which gives a generalised breakdown of the main report.
For the gender of 'Ladies', there may be several parea's so I want to sum
the packs_req for ladies only. (Gender is not a field I can use,...
| |
by: gbb0330 |
last post by:
Hi All
i am trying to calculate Quantity on hand in a unbound textbox in a
form with a subform
QonHand=Qreceived - TotalQSold
TotalQsold=QsoldOnEbay+QsoldThruOtherChannels
here is my expression:
|
by: potassium flower |
last post by:
I am trying to create a food order system for a restaurant.
I have tried using both the DSum and Sum functions to calculate the total cost of an order. The total cost is a textbox on the form tblOrder (and a field in tblOrder) and the list of meals along with their costs are in the tblOrderDetail Subform (the columns in this form come fromt two tables - tblOrderDetails and tblItems).
I have been putting the DSum / Sum in the control...
|
by: danielgoss |
last post by:
Hi
I have a report that has loads of textboxes that calculate things based on the value on another textbox in the report. I have put a hidden textbox on my report that gets its value from an inputbox when the report opens. I then want to use the value inputted in a DSUM calculation on the same form.
The hidden textbox is called tbHolddate, its control source is:
=InputBox()
The textbox that calculates a value using a DSUM control...
|
by: majapa |
last post by:
To start, here is my DSum expression:
Develop: Nz(DSum(". & "] + . & "]","CSO","='Ability to Develop'")*-1,0)
I actually have 4 of these, each one with a different option for .
For more context, the . part is drawing from a separate reference table which cycles through plugging in the name for each DB_ and Ben_ field in the CSO table.
What I want to do is add another layer of criteria, the field which exists in the same CSO table. I'd...
|
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: 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: 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: 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: 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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |