Calculated Control (Text Box) in a Report

[penjddj1]

(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)

[MMcCarthy]

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)
 

[penjddj1]

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]

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

[penjddj1]

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]

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)
 

[penjddj1]

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]

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.

[penjddj1]

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]

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.

[penjddj1]

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.

I believe Me! did not work because I have spaces in my subreport titles (although maybe I could have just used _).

[NeoPa]

I never use spaces in names myself as they invariably add difficulties. However, in this case, I can't see they would be used at all if you use the Me! reference. Where they are used, of course, you can always surround each name with [] characters.
I'm still intrigued that the Me! reference could not be made to work - for my learning and experience as well as for yours.

[penjddj1]

I never use spaces in names myself as they invariably add difficulties. However, in this case, I can't see they would be used at all if you use the Me! reference. Where they are used, of course, you can always surround each name with [] characters.
I'm still intrigued that the Me! reference could not be made to work - for my learning and experience as well as for yours.

When I use Me!, I receive a request for a parameter value for Me when I run the report. Perhaps Access doesn't like the mix of brackets/non-brackets - but that is just my guess. (I could also be typing something incorrectly, but can't see the tree for the forest as it were.)

[NeoPa]

Well, I suppose we'll have to leave it there.
I can't understand why it would ask for Me on its own in this case, but it's not important enough to waste any more time on. You have a solution that works. That's the important thing.