sum (1 Viewer)

AN60

Registered User.
Local time
Today, 05:59
Joined
Oct 25, 2003
Messages
283
I am having difficulties with a report used to view fertilizer elements. The report is based on a qry in which I have created several fields where the total amount of fertilizer is divided & multiplied to arrive at a %. This works ok. These Text Box fields (percentages) are placed into the report, so far so good. I then sum the text boxes, for example I have three text boxes for Sulphur content of three different fertilizers. Textbox:fertilizer1S, Textbox:fertilizer2S, Textbox:fertilizer3S
My report will sum each of the above just fine. My problem arises when I want to sum the three text boxes shown above. I have tried adding the three in the report footer after summing in the detail section or report footer ( many ways) & usually end up with nothing showing at all. I must be missing something small, hopefully.
 
R

Rich

Guest
you have to repeat the calculation =Sum(fert1+fert2+fert3) etc
 

AN60

Registered User.
Local time
Today, 05:59
Joined
Oct 25, 2003
Messages
283
Rich I have repeated the calculations as you suggested, prior to my original post & after, but with no luck. From what I can see I am only getting data from the first txt box. For some reason the other two are not added? Does it make any difference that the data originated from a calculation done in a new query column (not based directly on a tbl field)?
 

AN60

Registered User.
Local time
Today, 05:59
Joined
Oct 25, 2003
Messages
283
This still has me stumped????
After a bit more hair tugging I have noted that when the data from any of the three fields I am trying to sum contains a zero then the sum will always equal zero. eg. if field a = 1 and field b = 2 and field c=0 then in my report a+b+c= 0 not 3 as would be expected. Until now I have had no problem summing data in reports but this one is unusual.
 

databasedonr

Registered User.
Local time
Today, 01:59
Joined
Feb 13, 2003
Messages
163
Me Too - With Answer

AN60 -- I was trying to do the same thing, with the same results. Here's what I did:

If any of the fields could potentially come up as Null, then the addition doesn't work. In the the field that could contain a null, use an IIF statement to evaluate the null and assign a zero. In my database, I used this:

=IIf(IsNull(Sum([Unclass Mgmt])),0,(Sum([Unclass Mgmt])))

once the zero is in the field (rather than the null), it computes just fine.

To be specific, I was adding the following:

=[Reports]![rptName]![txtMgmt]+[Reports]![rptName]![txtOMs]+[Reports]![rptName]![txtCOs]+[Reports]![rptName]![txtOther]

In the field txtMgmt, I entered the IIF statement to handle the potential for nulls, and it solved the problem!

Good luck, and hope this helps!
 
Last edited:

AN60

Registered User.
Local time
Today, 05:59
Joined
Oct 25, 2003
Messages
283
databasedonr
Yes you are correct. I have been engaged on other matters until a few days ago when I revisited my query & report in question. Like you I ensured that every field contained a zero and was not null with this,
IIf([field] Is Null,0,[field]) . Now it all works 100%. Funny I knew about that null thingie before too, but I was working late at nights & had obviously not seen it as my problem. Must be "old age" or something. Thank you for your reply.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:59
Joined
Feb 19, 2002
Messages
42,981
Actually -
Sum(Nz([Unclass Mgmt]),0)

You want to get rid of the nulls in the field prior to summing.
 

AN60

Registered User.
Local time
Today, 05:59
Joined
Oct 25, 2003
Messages
283
PH what you suggest looks good so I will use that in future. A simpler way of doing things. Thank you all for your replies.
 

Users who are viewing this thread

Top Bottom