Displaying results according to user choice

[-New-]

I would need to display a certain result in a field depending on what the user selects.

Say, for eg, there are 3 fields.

Field 1 choices: 1, 2, 3
Field 2 choices: 1, 2, 3
Field 3 choices: 1, 2, 3

I'll need to display field 4 results according to what the user selects in field 1 to field 3.



Field 4 choices is: Low, moderate, high.

When the user selects a total of 3 points from field 1 to 3, field 4 shows Low.
When the user selects a total of 4-6 points from field 1 to 3, field 4 shows Moderate.
When the user selects a total of 7-9 points from field 1 to 3, field 4 shows High.

How can I complete the query?

[jgelpi16]

To me this doesn't sound like something you want to do in a query. Without seeing your DB in more detail I would recommend you use VBA....

var1 = Field1.Value
var2 = Field2.Value
var3 = Field3.Value

If var1+var2+var3 < 4 then
     Field4.Value = Low
End If
If var1+var2+var3 > 3 and var1+var2+var3 < 7 then
     Field4.Value = Medium
Else
     Field4.Value = High
End If

[-New-]

To me this doesn't sound like something you want to do in a query. Without seeing your DB in more detail I would recommend you use VBA....

var1 = Field1.Value
var2 = Field2.Value
var3 = Field3.Value

If var1+var2+var3 < 4 then
     Field4.Value = Low
End If
If var1+var2+var3 > 3 and var1+var2+var3 < 7 then
     Field4.Value = Medium
Else
     Field4.Value = High
End If

Is it possible to do it with IIF statements in the query?

Field 1 to 4 will be displayed on a form, if this helps

** To add things on, I'll like to have the results added into a table, as I'll need to display the results using Field 4 as a criteria to the Report.

And thanks for the reply


** Edit: How do I upload a sample database for you to peruse?

[-New-]

To me this doesn't sound like something you want to do in a query. Without seeing your DB in more detail I would recommend you use VBA....

var1 = Field1.Value
var2 = Field2.Value
var3 = Field3.Value

If var1+var2+var3 < 4 then
     Field4.Value = Low
End If
If var1+var2+var3 > 3 and var1+var2+var3 < 7 then
     Field4.Value = Medium
Else
     Field4.Value = High
End If

To provide you with more information, the values for the fields is as follows:

Field 1: Lacking, Moderate, Good
Field 2: Confirm, suspected, Nil
Field 3: Confirm, suspected, Nil

Lacking = 1 point, moderate = 2, good = 3
For both Field 2 and 3, Nil = 1 point, suspected = 2, confirm = 3

Field 4 will display the values as such:

When Field 1 = lacking and field 2 = Nil and field 3 = Nil, then field 4 = low

So when the results in field 1 to 3 adds up to 4-6 points, field 4 will display medium.