Formula

S

Susie

Hi....I want to have a formula in a report that will add
an amount only if the plan description is equal to 2
specific plans. This is the formula that I have written,
and does not work. I have tried other variations, also
with no luck. Can you look at my formula and tell me what
I am doing wrong, or if this is even possible. Thank you
in advance for your help.

=(IIf([plan_description]="oph sa 2005 - div" And iif
([plan_description]= "oph eg 2005 - div",Sum
([Plan_Annual_Budget_Amt]),0)))
 
G

Guest

hi,
here is an example of a iif formula i use. it gets the
data from one table if true another table if false.
it works.
IIf(IsNull([WODates]![Issue Date]) And [WKO]!
[WKO_StatusCode]="Released",[WKO]!
[WKO_StatusCode],"Issued" & "-" & [WODates]![Issue Date])
 
S

Susie

Thanks for the reply, but this will not work in my
situation. The information is in the same table and the
same field. I just want to add the budget amounts for
only 2 plans out of many.
-----Original Message-----
hi,
here is an example of a iif formula i use. it gets the
data from one table if true another table if false.
it works.
IIf(IsNull([WODates]![Issue Date]) And [WKO]!
[WKO_StatusCode]="Released",[WKO]!
[WKO_StatusCode],"Issued" & "-" & [WODates]![Issue Date])
-----Original Message-----
Hi....I want to have a formula in a report that will add
an amount only if the plan description is equal to 2
specific plans. This is the formula that I have written,
and does not work. I have tried other variations, also
with no luck. Can you look at my formula and tell me what
I am doing wrong, or if this is even possible. Thank you
in advance for your help.

=(IIf([plan_description]="oph sa 2005 - div" And iif
([plan_description]= "oph eg 2005 - div",Sum
([Plan_Annual_Budget_Amt]),0)))
.
Click to expand...
.
Click to expand...
 
J

John Vinson

Hi....I want to have a formula in a report that will add
an amount only if the plan description is equal to 2
specific plans. This is the formula that I have written,
and does not work. I have tried other variations, also
with no luck. Can you look at my formula and tell me what
I am doing wrong, or if this is even possible. Thank you
in advance for your help.

=(IIf([plan_description]="oph sa 2005 - div" And iif
([plan_description]= "oph eg 2005 - div",Sum
([Plan_Annual_Budget_Amt]),0)))
Click to expand...

You're misunderstanding the IIF function.

It takes three argurments:
1. An expression which evaluates to either TRUE or FALSE
2. A variant value returned if (1) is TRUE
3. A variant value returned if (1) is FALSE

Secondly, you seem to be making a very common error in BOOLEAN logic.
If the value of [plan_description] is equal to "oph sa 2005 - div"
then you can be ABSOLUTELY CERTAIN that it is not "oph eg 2005 - div",
so AND logic will always tell you "no, this is not true". The boolean
operators AND and OR *resemble* the English language conjunctions
"and" and "or", but they are much more precise mathematical operators!

Try

IIF([Plan_Description] = "oph sa 2005 - div" OR [Plan_Description] =
"oph eg 2005 - div", Sum(([Plan_Annual_Budget_Amt]), 0)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
S

Susie

Thanks, I have tried that formula, but I get the sum of
all the plans, not just the two I am trying to get the
sums for. It seems the logic there is saying, if the
Plan_description = either of the two, then sum the
Plan_annual_budget_amt for all. If you or anyone has any
other suggestions, I would more than appreciate anything
you can give me. Thanks!
-----Original Message-----
Hi....I want to have a formula in a report that will add
an amount only if the plan description is equal to 2
specific plans. This is the formula that I have written,
and does not work. I have tried other variations, also
with no luck. Can you look at my formula and tell me what
I am doing wrong, or if this is even possible. Thank you
in advance for your help.

=(IIf([plan_description]="oph sa 2005 - div" And iif
([plan_description]= "oph eg 2005 - div",Sum
([Plan_Annual_Budget_Amt]),0)))
Click to expand...

You're misunderstanding the IIF function.

It takes three argurments:
1. An expression which evaluates to either TRUE or FALSE
2. A variant value returned if (1) is TRUE
3. A variant value returned if (1) is FALSE

Secondly, you seem to be making a very common error in BOOLEAN logic.
If the value of [plan_description] is equal to "oph sa 2005 - div"
then you can be ABSOLUTELY CERTAIN that it is not "oph eg 2005 - div",
so AND logic will always tell you "no, this is not true". The boolean
operators AND and OR *resemble* the English language conjunctions
"and" and "or", but they are much more precise mathematical operators!

Try

IIF([Plan_Description] = "oph sa 2005 - div" OR [Plan_Description] =
"oph eg 2005 - div", Sum(([Plan_Annual_Budget_Amt]), 0)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
.
Click to expand...
 
J

John Vinson

Thanks, I have tried that formula, but I get the sum of
all the plans, not just the two I am trying to get the
sums for. It seems the logic there is saying, if the
Plan_description = either of the two, then sum the
Plan_annual_budget_amt for all. If you or anyone has any
other suggestions, I would more than appreciate anything
you can give me. Thanks!
Click to expand...

Well, you're getting that because it's precisely what you're asking
for.

IIF([Plan_Description] = "oph sa 2005 - div" OR [Plan_Description] =
"oph eg 2005 - div", Sum(([Plan_Annual_Budget_Amt]), 0)

If the Description is one of the selected values, return the Sum of
all values of [Plan_Annual_Budget_Amt. If Description is anything
else, return 0.

Try this instead. In the Query upon which this report is based, put
the IIF function

OKToSum: IIF([Plan_Description] = "oph sa 2005 - div" OR
[Plan_Description] = "oph eg 2005 - div", [Plan_Annual_Budget_Amt], 0)

Then Sum the value of OKToSum (rather than summing
Plan_Annual_Budget_Amt) on your report.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top