(Please forgive any misuse of terms in my description as this is my first post.)
I have created a text box (in a report) that is used to find a percentage based on two other controls. The calculation works fine unless there is a null value in both of the other controls. I would like to find a way to return either a 0 or bit of text (such as "N/A") whenever this situation arises. Currently, I receive the #Num! error.
I have seen posts which discuss how to pass a custom error message through a message box, but I would like mine to appear in the text box. Also, I cannot seem to find the error code number for #Num!, as I do not have the Microsoft Help Add-In avialable to me that provides the information, and I have not been able to find it searching the Web (if this error does indeed have a number).
Any assistance would be appreciated. My code appears below: - =Nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0)/Nz((Nz([Reports]![Main Report]![SubReport2].[Report]![Jan])+Nz([Reports]![Main Report]![SubReport1].[Report]![Jan])),1)
13  2905  MMcCarthy 14,534
Recognized Expert Moderator MVP
This error occurs most frequently when you try to divide by 0. Something like the following should work. -
=IIf(Not IsNull([Reports]![Main Report]![SubReport2].[Report]![Jan]),
-
Nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0) / (([Reports]![Main Report]![SubReport2].[Report]![Jan]) +
-
([Reports]![Main Report]![SubReport1].[Report]![Jan])),0)
-
First, thank you for your help with this. I tried your suggestion, but I now receive an "Enter Parameter Value" request for the IIF statement. I've tried changing the arguments around, but to no avail.
Also, I'm curious as to why the Nz Statements were taken out of the denominator given that SubReport1 could also be null, causing an error. I'm having a little trouble following the logic, but freely admit that I'm no wiz in that department.
Thanks again.
Here's my code as it appears now: - =Iff(Not IsNull([Reports]![Main Report]![SubReport2].[Report]![Jan]),
-
Nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0)/
-
(([Reports]![Main Report]![SubReport2].[Report]![Jan])
-
+([Reports]![Main Report]![SubReport1].[Report]![Jan])),0)
MMcCarthy 14,534
Recognized Expert Moderator MVP
First, thank you for your help with this. I tried your suggestion, but I now receive an "Enter Parameter Value" request for the IIF statement. I've tried changing the arguments around, but to no avail.
Also, I'm curious as to why the Nz Statements were taken out of the denominator given that SubReport1 could also be null, causing an error. I'm having a little trouble following the logic, but freely admit that I'm no wiz in that department.
I've put them back for SubReport1. Not sure why you're adding Jan field from subreport1 but have also included it in the false statement of the IIf.
Try this ... -
=Iff(Not IsNull([Reports]![Main Report]![SubReport2].[Report]![Jan]),
-
nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0)/
-
[Reports]![Main Report]![SubReport2].[Report]![Jan]
-
+nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0),
-
0+nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0))
-
Mary
I've put them back for SubReport1. Not sure why you're adding Jan field from subreport1 but have also included it in the false statement of the IIf.
Try this ...
-
=Iff(Not IsNull([Reports]![Main Report]![SubReport2].[Report]![Jan]),
-
nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0)/
-
[Reports]![Main Report]![SubReport2].[Report]![Jan]
-
+nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0),
-
0+nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0))
-
Mary
Ok, I've tried this and it does give me a 0 result if both controls are null, but gives me incorrect percentages if one or both are not null (basically acts if the denominator does not exist.)
Because you asked why I added the Jan field from subreport 1, I thought it might be useful to break the problem down into the simplest form possible. Therefore, this is what I'm trying to do:
I have two reports, say A and B, which include a count of records that meet a particular criteria (True-for A or False-for B) per month. I am trying to create a text box that calculates %True out of True+False for a given month. Using the Nz function, my original calculation (basically, T/(T+F), worked except for the occasion when there were no True or False records for a particular month.
I hope this makes things a bit clearer. I'm sure that there may even be a simpler way to go about solving this problem that I've missed.
I appreciate your continued assistance.
MMcCarthy 14,534
Recognized Expert Moderator MVP
Ok, that helps a lot. Try this ... -
=Iff(nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0) + nz([Reports]![Main Report]![SubReport2].[Report]![Jan],0) <> 0,
-
nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0) / (nz([Reports]![Main Report]![SubReport1].[Report]![Jan], 0) + nz([Reports]![Main Report]![SubReport2].[Report]![Jan],0)),
-
nz([Reports]![Main Report]![SubReport1].[Report]![Jan],0) /1)
-
Ok, I will try that on Monday (they've sent us home for the weekend) and let you know how it works. I do really appreciate your help.
NeoPa 32,579
Recognized Expert Moderator MVP
You may want to try this version : - =Nz(Me!SubReport2!Jan,0)/
-
IIf(Nz(Me!SubReport1!Jan,0)+
-
Nz(Me!SubReport2!Jan,0)=0,1,
-
Nz(Me!SubReport1!Jan,0)+
-
Nz(Me!SubReport2!Jan,0))
Line breaks are for display purposes only and should be removed before testing.
NB. When referring to items within the same report (I'm assuming this is the case here) you can use Me.
Ok, I tried both of the examples above, and found that the following code makes the equation work: - =Nz([Reports]![Main Report]![Subreport1].[Report]![Jan],0)
-
/IIf(Nz([Reports]![Main Report]![Subreport2].[Report]![Jan],0)
-
+Nz([Reports]![Main Report]![Subreport1].[Report]![Jan],0)=0,
-
Nz([Reports]![Main Report]![Subreport1].[Report]![Jan],1),
-
Nz([Reports]![Main Report]![Subreport1].[Report]![Jan],0)
-
+Nz([Reports]![Main Report]![Subreport2].[Report]![Jan],0))
I thank both of you for your assistance with this.
NeoPa 32,579
Recognized Expert Moderator MVP
Is the control not on the same report then. Otherwise I can't see why you'd need the references to be so complicated.
This is not a critical issue if you have it working, but it would certainly make it easier for you to work with in future if it is tidied up.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Derek Erb |
last post by:
I have a field in a report which is filled by a function. This is
because I want the values displayed in a particular way.
The field is filled with:
strVal = Format(ValLow, "$#,###") & " / " & Format(ValHi, "$#,###")
The difficulty lies when the concatenated string is too long to fit on
one line and needs to therefore fit on two lines. The detail section
is CanGrow and CanShrink, every field in the secton is CanGrow and
CanShrink. ...
|
by: jdph40 |
last post by:
I've been given a database and asked to figure out how to sort a
report. The report needs to be sorted on a field that is the sum of a
field that is calculated in the underlying query. NetSales is
calculated in the query; the control source of the text box on the
report is Sum(). I need to sort on this. I tried going to
the query and creating a new field - TotalNetSales: Sum(),
but I get an error message about "cannot have aggregate...
|
by: Paolo |
last post by:
Friends, I have a table with a field named Initials which has its
record source to another table named Initials.
I would like to add on a form named Welcome two controls:
A combo box, which displays the initials of all my database users and
a text box which calculated the number of records associated to the
selected user.
I would like to use the OnChange event of the combo to run the query
or the calculation code.
|
by: shobhit_shanker |
last post by:
Here are the relevant "givens" to my problem...
Form: frmLaunchRpt
- Text Box: txtAsOfDate
- Check Box: chkLogEval
- Command Button: cmdLaunchRpt
Report: rptEvaluation
- Text Box: txtGreen (Percent of items on schedule)
- Text Box: txtYellow (Percent of items slightly off schedule)
|
by: Dave |
last post by:
This one's pretty basic, but I really need help. I have two tables,
one with customer information and another which looks up prices
depending upon size in united inches.
It works fine if I input the united inches value in the form, but if it
is a calculated field based on width plus height (which is united
inches), it retain the united inches value.
| | |
by: Shannan Casteel via AccessMonster.com |
last post by:
I have a subform named "sbfrmParts" with a list of parts along with the
quantity and price. I have used a text box in the subform's footer and set
the control source to "=Sum(*)". I set the format to
display currency. This text box is called "SubformTotal", and is visible
property is set to "No". On the main form I have made another text box and
set its control source to "=.Form!SubformTotal".
When I enter some parts everything works...
|
by: Zlatko Matiæ |
last post by:
I have a field and want to have a calculated field in
a report that counts it. It had control source set to "=count
(). As can be also Null, I would like
it to count only values distinct from Null. I tried by
"Control Source=Count( Is Not Null), but it doesn't work, it still
counts all rows, even with Null.
How to set Control Source in the calcualted text box, in order to count only
values that are not null?
Thanks,
|
by: administrator |
last post by:
Hi,
I'm having trouble with a calculated text box.
"= & & & & &
& & & & &
& & & & "
This produces the correct results, but I want it to create each outcome
below the previous one, and to be honest I'm stumped.
|
by: Olveres |
last post by:
Hi,
I have managed to work out how to add new lines into a calculated text
box. However in this text box some of the outcome fields are empty
however when previewing the report it includes the blank fields, so
each section of the report is the same size, my field is set to can
grow/shrink, but I think my inclusion in the code for the calculated
box of all 15 outcomes (I have no choice) is what's causing each
calculated box to be the...
|
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: 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: 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: 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.
| |
