Link to home
Start Free TrialLog in
Avatar of joylene6
joylene6Flag for United States of America

asked on

Help with tweaking nested IIF statement

Hello - need assistance with writing a nested IIF statement in a MSACCESS query

Here is what I am trying to do:

If COMPONENT = MED, then I need to look at the difference between REFILLS ALLOWED and REFILLS REMAINING.  If they are equal numbers the result will be START. If they are not equal, then result should be "REFILL"

If COMPONENT IS NOT "MED", then need to look at the difference between REFILLS ALLOWED and REFILLS REMAINING.  If the difference between  REFILLS ALLOWED and REFILLS REMAINING is -1, Then  the result will be START. If the difference is more than 2, then result should be "REFILL".  

Note: REFILLS ALLOWED will always have a bigger number than Refills Remaining.

I tried.... but this does not work....
STATUS: IIf([COMPONENT]= "MED"& [REFILLS_ALLOWED] = [REFILLS_REMAINING], "START",REFILL",IIf([COMPONENT] NOT "MED" & [REFILLS_ALLOWED] = [REFILLS_REMAINING] -1, "START", IIf([COMPONENT] NOT "MED" & [REFILLS_ALLOWED] = [REFILLS_REMAINING] >-2, "REFILL",

EXAMPLE
Component    Refills Allowed         Refills Remaining             Status
   MED                    3                                    3                                START
    SOL                     3                                    2                                START
    MED                   3                                     1                                 REFILL
    MED                   2                                     0                                REFILL
    ADD                    5                                     4                                START
    ADD                     4                                    2                               REFILL                  
    MED                    1                                     1                              START
    MED                     1                                    0                               REFILL
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of joylene6

ASKER

Thank you!!