=sum() function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any reason why this formula wouldn't work?

=(nz([sumw])+([sumo])+([sumf])+([sumh]))

It works for all values of sumw, sumo, sumf, but not for sumh.

sumw is the sum of all records in a table where a field = w
sumo is the sum of all records in a table whre a field = o
....

there is bound field on my report for h and its name is sumh, just as for w,
o, and f. I know that there is a value for h but the sum() doesn't capture
it.

Does someone see something that I'm missing here?

tia,
 
Are these all fields in your report's record source? If they are control
names then
-what are their control sources
-where are all the controls located (which section(s))
-are any value possibly null?
Do you realize that only the SumW is handled for Null values and I don't
agree with the syntax. I recommend using Nz([FieldName],0)
 
All fields are in report's record source
control sources are h, s, a, c, etc
all fields are in the detail section, sum values are in header section and
are named sumh, sums, suma, sumc, ect. with each control source as
=sum(nz([field],0)) etc. to take care of any null values there.

On the sum of all the sums I use =nz([sumh])+([sums])...) etc. and it
doesn't work. Tried to use =sum((nz([sumh],0)+(nz([sums],0))...)) etc. and
it doesn't work either.

Is what I'm trying to do even possible?
ta,
JMorrell


Duane Hookom said:
Are these all fields in your report's record source? If they are control
names then
-what are their control sources
-where are all the controls located (which section(s))
-are any value possibly null?
Do you realize that only the SumW is handled for Null values and I don't
agree with the syntax. I recommend using Nz([FieldName],0)

--
Duane Hookom
MS Access MVP


JMorrell said:
Is there any reason why this formula wouldn't work?

=(nz([sumw])+([sumo])+([sumf])+([sumh]))

It works for all values of sumw, sumo, sumf, but not for sumh.

sumw is the sum of all records in a table where a field = w
sumo is the sum of all records in a table whre a field = o
...

there is bound field on my report for h and its name is sumh, just as for w,
o, and f. I know that there is a value for h but the sum() doesn't capture
it.

Does someone see something that I'm missing here?

tia,
Click to expand...
Click to expand...
 
You can't sum controls from one section to another. You can try:
=Sum(Nz(h,0))+Sum(Nz(s,0))+Sum(Nz(a,0))+Sum(Nz(c,0)) ... etc

Sometimes summing across fields suggests un-normalized tables.

--
Duane Hookom
MS Access MVP
--

JMorrell said:
All fields are in report's record source
control sources are h, s, a, c, etc
all fields are in the detail section, sum values are in header section and
are named sumh, sums, suma, sumc, ect. with each control source as
=sum(nz([field],0)) etc. to take care of any null values there.

On the sum of all the sums I use =nz([sumh])+([sums])...) etc. and it
doesn't work. Tried to use =sum((nz([sumh],0)+(nz([sums],0))...)) etc. and
it doesn't work either.

Is what I'm trying to do even possible?
ta,
JMorrell


Duane Hookom said:
Are these all fields in your report's record source? If they are control
names then
-what are their control sources
-where are all the controls located (which section(s))
-are any value possibly null?
Do you realize that only the SumW is handled for Null values and I don't
agree with the syntax. I recommend using Nz([FieldName],0)

--
Duane Hookom
MS Access MVP


JMorrell said:
Is there any reason why this formula wouldn't work?

=(nz([sumw])+([sumo])+([sumf])+([sumh]))

It works for all values of sumw, sumo, sumf, but not for sumh.

sumw is the sum of all records in a table where a field = w
sumo is the sum of all records in a table whre a field = o
...

there is bound field on my report for h and its name is sumh, just as
Click to expand...
for
w,
o, and f. I know that there is a value for h but the sum() doesn't capture
it.

Does someone see something that I'm missing here?

tia,
Click to expand...
Click to expand...
Click to expand...
 
YES! this is what I needed. It is actually

=Sum(Nz([h],0))+Sum(Nz([p],0))+Sum(Nz([o],0))+Sum(Nz([f],0))+Sum(nz([w],0))

but yes, that does the trick.

thanks a ton!

JMorrell

Duane Hookom said:
You can't sum controls from one section to another. You can try:
=Sum(Nz(h,0))+Sum(Nz(s,0))+Sum(Nz(a,0))+Sum(Nz(c,0)) ... etc

Sometimes summing across fields suggests un-normalized tables.

--
Duane Hookom
MS Access MVP
--

JMorrell said:
All fields are in report's record source
control sources are h, s, a, c, etc
all fields are in the detail section, sum values are in header section and
are named sumh, sums, suma, sumc, ect. with each control source as
=sum(nz([field],0)) etc. to take care of any null values there.

On the sum of all the sums I use =nz([sumh])+([sums])...) etc. and it
doesn't work. Tried to use =sum((nz([sumh],0)+(nz([sums],0))...)) etc. and
it doesn't work either.

Is what I'm trying to do even possible?
ta,
JMorrell


Duane Hookom said:
Are these all fields in your report's record source? If they are control
names then
-what are their control sources
-where are all the controls located (which section(s))
-are any value possibly null?
Do you realize that only the SumW is handled for Null values and I don't
agree with the syntax. I recommend using Nz([FieldName],0)

--
Duane Hookom
MS Access MVP


Is there any reason why this formula wouldn't work?

=(nz([sumw])+([sumo])+([sumf])+([sumh]))

It works for all values of sumw, sumo, sumf, but not for sumh.

sumw is the sum of all records in a table where a field = w
sumo is the sum of all records in a table whre a field = o
...

there is bound field on my report for h and its name is sumh, just as for
w,
o, and f. I know that there is a value for h but the sum() doesn't
capture
it.

Does someone see something that I'm missing here?

tia,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top