IIF statement

[hoachen]

I have want to specify certain keyword and put them a value. For
example:keyword "CD", "Book", "newspaper" to have a value 0 which i can
accomplish by using the iif
Expr1: IIf([Keyword]="CD","0",IIf([Keyword]="Book","0"),
([Net]/0..1*0.55*1.1))

What if just say iif not these (CD, Book, newspaper) then put a value of -1?

 

[Duane Hookom]

This type of logic should be built using values in tables. Hard coding values
like "CD" and "Book" into expressions in queries or other places will only
lead to high maintenance applications.

If you can't figure out how to use values stored in tables, you should at
least consider creating small user-defined functions for this logic. Save the
function in a module named "modBusinessCalcs".

 

[Dorian]

I agree with what Duane said, however, as to your question, you don't need to
use multiple IIF, you can use AND and OR in your condition.
e.g.
IIF(cond1 and cond2,x,y)
or
IIF(cond1 or cond2,x,y)
or
IIF(not (cond1) and not(cond2),x,y)
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[hoachen]

I agreed too, but i have no choice!
I tried the OR/AND but does not work. Can you tell why is that?
Expr1: IIf([Keyword]=BOOK" or [Non-Text]="CD" Or
[Non-Text]="newspaper","0","17",IIf([price] Is Not Null,"0",IIf([Status] Is
Not Null,"0",IIf([Net]<>0 And [Disc]=1,([Net]/0.75*0.25*1.8),IIf([Net]<>0 And
[Disc]=2 And

• >8,([Net]/0.75*0.15*1.8))))
  
  Like i said, if the keyword equal to "BOOK", "CD" or other text that i
  specify then put a 0 value and other put a -7 value.
  
  

  I agree with what Duane said, however, as to your question, you don't need to
  use multiple IIF, you can use AND and OR in your condition.
  e.g.
  IIF(cond1 and cond2,x,y)
  or
  IIF(cond1 or cond2,x,y)
  or
  IIF(not (cond1) and not(cond2),x,y)
  -- Dorian
  "Give someone a fish and they eat for a day; teach someone to fish and they
  eat for a lifetime".
  
  
  

  I have want to specify certain keyword and put them a value. For
  example:keyword "CD", "Book", "newspaper" to have a value 0 which i can
  accomplish by using the iif
  Expr1: IIf([Keyword]="CD","0",IIf([Keyword]="Book","0"),
  ([Net]/0..1*0.55*1.1))
  
  What if just say iif not these (CD, Book, newspaper) then put a value of -1?

 

[John W. Vinson]

I agreed too, but i have no choice!
I tried the OR/AND but does not work. Can you tell why is that?
Expr1: IIf([Keyword]=BOOK" or [Non-Text]="CD" Or
[Non-Text]="newspaper","0","17",IIf([price] Is Not Null,"0",IIf([Status] Is
Not Null,"0",IIf([Net]<>0 And [Disc]=1,([Net]/0.75*0.25*1.8),IIf([Net]<>0 And
[Disc]=2 And

• >8,([Net]/0.75*0.15*1.8))))
  
  Like i said, if the keyword equal to "BOOK", "CD" or other text that i
  specify then put a 0 value and other put a -7 value.

• 
  
  You've got four arguments to the first IIF not 3; nowhere do you have a -7
  (should that be 17, or should the 17 be -7)? What are all the other IIF's
  nested in here?
  
  Step back a bit. What's in your table? What is the context (where is this mess
  of IIFs)? What exactly - in words, in narrative text - are you trying to
  accomplish?

 

[hoachen]

What i am trying to do is:

Field consists of: Keyword, list price, net price, Calculated Price,
discount (1 or 2).

If the keyword IS "CD", "Book" or "Newspaper" Place a value zero on the
Calculated field.
If the keyword other than the above, then evaluate other fields.
1st field to evaluate is net price, if net price is not 0 (zero) and
the discount is 1 then use this calculation and place the value on the
Calculated field.
2nd, if the net price is not 0(zero) and the discount is 2, then use
this calcualtion and place the value on Calculated field.

And then if the calculated field is 0 or null, then evaluate the discount
field, if the discount field is 1, use this calculation and place the value
on the Calculated field.

Final, if the calculated field is 0 or null, then evaluate the discount
field, if the discount field is 2, use this calculation and place the value
on the Calculated field.

Are this make sense?

I agreed too, but i have no choice!
I tried the OR/AND but does not work. Can you tell why is that?
Expr1: IIf([Keyword]=BOOK" or [Non-Text]="CD" Or
[Non-Text]="newspaper","0","17",IIf([price] Is Not Null,"0",IIf([Status] Is
Not Null,"0",IIf([Net]<>0 And [Disc]=1,([Net]/0.75*0.25*1.8),IIf([Net]<>0 And
[Disc]=2 And

• >8,([Net]/0.75*0.15*1.8))))
  
  Like i said, if the keyword equal to "BOOK", "CD" or other text that i
  specify then put a 0 value and other put a -7 value.

• 
  
  You've got four arguments to the first IIF not 3; nowhere do you have a -7
  (should that be 17, or should the 17 be -7)? What are all the other IIF's
  nested in here?
  
  Step back a bit. What's in your table? What is the context (where is this mess
  of IIFs)? What exactly - in words, in narrative text - are you trying to
  accomplish?