Adding mutliple dsums

  • Thread starter Thread starter Jared
  • Start date Start date
J

Jared

Each of the follow Dsum works fine by itself but when I am trying to add them
together I only get the first one. Is this a restriction of DSUM, if so, do
you know of any other way to do it?

=DSum("WinnerScore","Query1","Winner = [Team
Name]")+DSum("LoserScore","Query1","Loser = [Team Name]")

Again, each one on its own works perfectly fine so I dont think the field
details are required.
 
I don't generally care for solutions that use DSum() in reports or queries.
Some times they are the only legitimate option.

However, you might try wrap each of the DSum()s in Val() like:
=Val(DSum("WinnerScore","Query1","Winner = """ & [Team Name] & """") +
Val(DSum("LoserScore","Query1","Loser = """ & [Team Name] & """"))

This assumes your Team Name field is text. If the field is numeric, you
should remove about 12 double-quotes from the expression.
 
Jared said:
Each of the follow Dsum works fine by itself but when I am trying to add them
together I only get the first one. Is this a restriction of DSUM, if so, do
you know of any other way to do it?

=DSum("WinnerScore","Query1","Winner = [Team
Name]")+DSum("LoserScore","Query1","Loser = [Team Name]")

Again, each one on its own works perfectly fine so I dont think the field
details are required.
Click to expand...


I don't know what you mean by "only get the first one"

If either DSum can not find any records to sum up, then its
result will be Null. Any value added to Null will result in
Null. If that's your situation, try using:

=Nz(DSum("WinnerScore","Query1","Winner = [Team Name]"), 0)
+ Nz(DSum("LoserScore","Query1","Loser = [Team Name]"), 0)
 
Marshall,

That worked great. I guess it did not like the Nulls adding to a second
number.
I appreciate you and Duane's help.

thanks

Jared

Marshall Barton said:
Jared said:
Each of the follow Dsum works fine by itself but when I am trying to add them
together I only get the first one. Is this a restriction of DSUM, if so, do
you know of any other way to do it?

=DSum("WinnerScore","Query1","Winner = [Team
Name]")+DSum("LoserScore","Query1","Loser = [Team Name]")

Again, each one on its own works perfectly fine so I dont think the field
details are required.
Click to expand...


I don't know what you mean by "only get the first one"

If either DSum can not find any records to sum up, then its
result will be Null. Any value added to Null will result in
Null. If that's your situation, try using:

=Nz(DSum("WinnerScore","Query1","Winner = [Team Name]"), 0)
+ Nz(DSum("LoserScore","Query1","Loser = [Team Name]"), 0)
Click to expand...
 
Back
Top