IIF statement

  • Thread starter Thread starter hoachen
  • Start date Start date
H

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?
 
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".
 
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 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.

    Dorian said:
    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 said:
    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?
    Click to expand...
    Click to expand...
 
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.
Click to expand...


  • 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?
 
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?

John W. Vinson said:
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.
Click to expand...


  • 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?
Click to expand...
 
Back
Top