joylene6
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([COMPO NENT] 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
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([COMPO
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER