Can't get totals right on report 1

[jnix]

I have four fields, a,b,c,d, that I'm summarizing on customer number in a report. I also want to summarize a total of the sums of a+b+c+d for each customer. I've used the formula =Sum([a])+sum()+sum([c])+sum([d]). If a,b,c and d are all present for a customer, the formula works okay. If any of the items is absent, the formula produces a blank. How can I fix this?
Thanks,
jnix

 

[jjonesal]

Can you be more specific:

is the =Sum([a])+sum()+sum([c])+sum([d]) in a control on the report or in the query? If on the report, is it in the detail section of the report or a group footer? J. Jones
jjones@cybrtyme.com

 

[Steve101]

Try the following:

=Sum(nz([a]))+sum(nz([b]))+sum(nz([c]))+sum(nz([d]))

what's happening is that if any of your fields contain a null value, then the total of a through d will evaluate to null; hense your blank result. The nz() function is very good for preventing this sort of situation.

Also, you may have noticed that your entry disappeared in the post, to be replaced by bolded test thereafter. When you are entering text into a thread, checdk out the 'Process TGML' option bottom right of the screen; it contains the codes that allow you to format your text, and will also help prevent you from inadvertently using these codes, and getting undesired formats (and losing real text). If unsure, untick the Process TGML option.

Hope this helps,
Cheers
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[jnix]

Steve:
Your answer works.
Thanks,
jnix