Mutliple IIf (I think!)

[Terry_C]

I want to enter a value automatically into a form field called "Risk Factor" based on the values entered in two previous fields. The two fields are "Probability" which has values of Low, Medium and High and "Impact" which also has values of Low, Medium and High. The values I want to enter automatically in the "Risk Factor" field will be determined by the combinations of the previous two fields and therefore I have a possible 9 combinations (predetermined) to check to provide values of Low, Medium and High. For example if you select a "Probability" of Medium and "Impact" as High I would want High to be automatically entered in the third (Risk Factor) field.

What is the easiest way (and I'm not an Access expert by any means!) to accomplish this please?

Thank you

 

[FoFa]

There a number of ways to do this, but I would create a table with the possabilities and results, then use something dlookup to get the value.

Results Table:
Risk Factor Value
Probability Value
Impact Value

Than later if you have to make changes, you just have to do it in a table. You can do it in code, multi IF's or a SELECT, but I am a fan of putting easy maint. and changability.
Just my 2 cents worth

 

[WayneRyan]

Terry,

Select Case Me.Probability
Case "Low"
Me.[Risk Factor] = Me.Impact
Case "Medium"
If Me.Impact <> "Low" Then
Me.[Risk Factor] = Me.Impact
End If
Case Else
Me.[Risk Factor] = "High'
End Select

Haven't tested, but its close ...

Wayne

 

[Terry_C]

Thanks - will try both of these later this morning.