Looks like you want nested
Iif
statements. Try:
Iif(((ActualWeight >= (.96 * TargetWeight)) And (ActualWeight <= (1.04 * TargetWeight))), 'GOOD', Iif(ActualWeight < (0.96 * TargetWeight), 'BAD', Iif(ActualWeight > (1.04 * TargetWeight), 'BAD', null)))
Putting the expression on multiple lines might make things clearer:
Iif(
((ActualWeight >= (.96 * TargetWeight)) And (ActualWeight <= (1.04 * TargetWeight))),
'GOOD',
Iif(
ActualWeight < (0.96 * TargetWeight),
'BAD',
Iif(
ActualWeight > (1.04 * TargetWeight),
'BAD',
null
)
)
)
EDIT: As Maciej Los pointed out, you can simplify the formula so that you don't need nested
Iif
s. See solution #3 for details.