Using DSum with multiple criteria.

[smcglone]

Hi,

I need to obtain a total for a group of people with 2 criteria. I need to
find the grand total of people in on a certain team if they are currently on
the treatment. I wasn't sure how to just exclude people who are not on
treatment, so I have an option for treatment where 1=yes and 2=no. I
basicially need a grand total for each team for only those recieving
treatment. I just need help with a DSum formula that will allow for both
criteria, here is what I have so far.

=DSum("(Val(Nz([age]))+Val(Nz([Language]))+Val(Nz([immigration]))+Val(Nz([Mental Health])))","Acuity test 2","[team]='1'" & "[Currently on Treatment]='1'")

The formula works until I add the last criteria (treatment). I may even be
going about this the wrong way. Any help would be great! Thanks!

 

[bcap]

=DSum("(Val(Nz([age]))+Val(Nz([Language]))+Val(Nz([immigration]))+Val(Nz([Mental
Health])))","Acuity test 2","[team]='1' AND [Currently on Treatment]='1'")

 

[Allen Browne]

The 2 conditions need AND between them, just like the WHERE clause in a
query:

=DSum("(Val(Nz([age],0))+Val(Nz([Language],0))+Val(Nz([immigration],0))+Val(Nz([Mental
Health],0)))",
"Acuity test 2",
"([team]='1') AND ([Currently on Treatment]='1')")

 

[smcglone]

Thank you both! It works now, go figure its the one thing I didn't try.

The 2 conditions need AND between them, just like the WHERE clause in a
query:

=DSum("(Val(Nz([age],0))+Val(Nz([Language],0))+Val(Nz([immigration],0))+Val(Nz([Mental
Health],0)))",
"Acuity test 2",
"([team]='1') AND ([Currently on Treatment]='1')")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I need to obtain a total for a group of people with 2 criteria. I need to
find the grand total of people in on a certain team if they are currently
on
the treatment. I wasn't sure how to just exclude people who are not on
treatment, so I have an option for treatment where 1=yes and 2=no. I
basicially need a grand total for each team for only those recieving
treatment. I just need help with a DSum formula that will allow for both
criteria, here is what I have so far.

=DSum("(Val(Nz([age]))+Val(Nz([Language]))+Val(Nz([immigration]))+Val(Nz([Mental
Health])))","Acuity test 2","[team]='1'" & "[Currently on Treatment]='1'")

The formula works until I add the last criteria (treatment). I may even
be
going about this the wrong way. Any help would be great! Thanks!