IIf Statement Query

[Dscalf1]

I am having a problem to get my query to run properly. I have a huge IIf statement that doesnt seem to be working but Im not sure what is wrong with it. What it is supposed to do is return a good, actionable, or poor based on a specified weight and an actual weight. So what the query does is pull information from a table based on the specified weight and then using that information it should give back the correct rating in the last column.

Here are the conditions:
Poor:
AW(Actionable Weight)<Lower Actionable Weight
AW> Upper Actionable Weight

Actionable:
Lower Actionable<AW<Lower Acceptable
Upper Acceptable<AW< Upper Actionable

Good:
Lower Acceptable<AW<Upper Acceptable

The lower acceptable, actionable, and upper acceptable, actionable are different numbers based on the specified weight entered.

Here is the IIf Statement I currently have and I wasnt sure if order mattered or what I was doing wrong..

Rating: IIf([LOWER ACTOINABLE]<[Actual Weight]<[LOWER ACCEPTABLE],"Actionable",(IIf([LOWER ACCEPTABLE]<[Actual Weight]<[UPPER ACCEPTABLE],"Good",(IIf([UPPER ACCEPTABLE]<[Actual Weight]<[UPPER ACTIONABLE],"Actionable","Poor")))))

 

[jdraw]

The syntax for IIF is
IIF(condition, TruePart, FalsePart)

What your condition is isn't clear. Are your < (less than) really less than, or could they be <= (less than or equal)?
You must state the criteria explicitly. ( I did some reformatting, but may have missed your intent re criteria)

eg

Rating: 
IIf([LOWER ACTOINABLE]<[Actual Weight] AND 
     [Actual Weight]<[LOWER ACCEPTABLE],"Actionable",
   (IIf([LOWER ACCEPTABLE]<[Actual Weight] AND 
         [Actual Weight]<[UPPER ACCEPTABLE],"Good",
         (IIf([UPPER ACCEPTABLE]<[Actual Weight] AND 
               [Actual Weight]<[UPPER ACTIONABLE],"Actionable","Poor"))
  )
 )
)

 

[arnelgp]

Rating: IIf([ACTUAL WEIGHT] > [LOWER ACTIONABLE] AND [ACTUAL WEIGHT] < [LOWER ACCEPTABLE], "Actionable", IIF([ACTUAL WEIGHT] > [LOWER ACCEPTABLE] AND [ACTUAL WEIGHT] < [UPPER ACCEPTABLE], "Good", IIF([ACTUAL WEIGHT] > [UPPER ACCEPTABLE] AND [ACTUAL WEIGHT] < [UPPER ACTIONABLE], "Actionable", "Poor")))

 

[Dscalf1]

Thank You so much I didnt even think about using an AND function in there it worked. Another quick question Im not sure is possible.. Is there anyway to implement this into a form. Say the user enters the specified weight and actual weight on a form can I put a button or something to either say good,actionable, or poor or anyway I could use colors to say this like green, yellow, and red?

 

[jdraw]

see Conditional formatting

 

[Dscalf1]

You can use conditional formatting based on a query? It has to be off the query because as the specified weight number gets bigger so does the different rating ranges..

I tried using conditional formatting on the text boxes at first but then i figured out that the ranges change. I tried to use a button that just pulls the query up and I changed {actual weight} to the textbox on the form but then the query doesnt work if i do that..

 

[Dscalf1]

The syntax for IIF is
IIF(condition, TruePart, FalsePart)

What your condition is isn't clear. Are your < (less than) really less than, or could they be <= (less than or equal)?

Rating:
IIf([LOWER ACTOINABLE]<[Actual Weight] AND
[Actual Weight]<[LOWER ACCEPTABLE],"Actionable",
(IIf([LOWER ACCEPTABLE]<[Actual Weight] AND
[Actual Weight]<[UPPER ACCEPTABLE],"Good",
(IIf([UPPER ACCEPTABLE]<[Actual Weight] AND
[Actual Weight]<[UPPER ACTIONABLE],"Actionable","Poor"))
)
)
)

This worked all except for when the number lands exactly on one of the lower acceptable, actionable and upper acceptable, actionable.

It is supposed to be a greater than (not a greater than or equal to)

For example:
Specified Weight=37
L. Acc=35
L. Act=33
U. Acc=39
U. Act= 41

Right now if the actual weight is any of the numbers above it returns "poor" and that isnt correct.

If the actual weight = 35 or 39 it should be good
If the actual weight = 33 or 41 it should be actionable