Nested "IIF" or....?

[Guest]

My question is quite complicated so I will try and explain it a simple as
possible

On a form, I have a set of radio buttons that selects the criteria I need to
use when deciding if something "Passes" or "Fails"

The the options are:

[1] 98 - 102 - a pass here would be between 98 and 102 otherwise fail
[2] 95 - a pass here is simply above 95

Selecting one of these would put a 1 or a 2 in the "Specification" field on
the table "Lot"

I have manged to sucessfully bring the specification field into my datasheet
using a simple query, but now I need to automate a control source [PassFail]
that will reference another calculated field [Characterisitc Value]

eg.

Criteria 1 has been chosen ie 98 - 102
and the Characteristic Value = 101.3
PassFail should say "Pass"

Should this be done using nested IIF statements, or is there an easier way?

Thanks in advance.......

 

[Allen Browne]

Phil, you need another table to handle the lookup values.

Typically the table has 2 fields: the minimum value (number) and a
description of that value (text), e.g.:
MinValue Descrip
0 Fail
95 Pass
98 Pass with flying colors

I'm not clear if you need a 3rd column as well, i.e. if you have ranges that
apply to different types of entity, e.g.:
EntityType MinValue Descrip
Widget 0 Fail
Widget 95 Pass
Foobar 0 Fail
Foobar 98 Pass

Anyway, once you have that table set up, you can create the query to return
the description that matches the actual test result (for the particular kind
of entity.) Tom Ellison explains how to do this here:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html