Stumped!!!

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

Guest

I am trying to return a sum of a calculated field "Weight loss" if the "Patient Status" is = to "SS" or "Ongoing" in a Patient Name footer. I am getting a number but not the correct one. It seems so easy this is what I have done that does not work

IIF([Patient Status]="SS" Or "Ongoing",Sum[Weight loss],"0")
 
You have to list the field twice. Here is one method...
=Sum( Abs([Patient Status]="SS" OR [Patient Status] = "Ongoing") * [Weight
Loss])
[Patient Status]="SS" OR [Patient Status] = "Ongoing" will return either -1
if true or 0 if false.

BTW: 55+ lbs over last 6 months with low carb. Thanks for asking ;-)
--
Duane Hookom
MS Access MVP


Linda said:
I am trying to return a sum of a calculated field "Weight loss" if the
Click to expand...
"Patient Status" is = to "SS" or "Ongoing" in a Patient Name footer. I am
getting a number but not the correct one. It seems so easy this is what I
have done that does not work
IIF([Patient Status]="SS" Or "Ongoing",Sum[Weight loss],"0")
Click to expand...
 
Linda,

There are a number of problems with your expression. Try this...

=Sum(IIf([Patient Status]="SS" Or [Patient Status]="Ongoing",[Weight
loss],0))

Alternative ways of expressing the same thing include...
=Sum(IIf([Patient Status] In("SS","Ongoing"),[Weight loss],0))
.... and...
=Abs(Sum([Patient Status] In("SS","Ongoing")*[Weight loss]))
 
Can you provide some sample records and the expected average?

--
Duane Hookom
MS Access MVP


Linda said:
Great job on the weight loss. Thanks! that worked great. Now how do I do
Click to expand...
an average of the weight loss from the name footer in the report footer. I
keep getting the average of the weekly weight loss instead of the total
weight average for every person?? I am obviously new to Access!!!
 
Back
Top