[need help] how to have multiple if conditions in a field

[shuto13]

Hello everyone,
sorry if I'm in the wrong room...

here is what I have



nine columns H,S,E,O, L1, L2, L3, L4, L5 and the results column "strategi"
H,S,E,O, L1, L2, L3, L4, L5 is my representation of a decision diagram, where H,S,E,O are the "fields of damage", and L1,L2,L3,L4,L5 are the "levels of severity". the fields are filled in limited to "Y" or "T".
what is going on is as such,
in the result field "strategi" I have this IIf code

IIf(
[L1]="Y","On Condition Maintenance",
IIf([L1]="T" And [L2]="Y","Preventive Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="Y","Corrective Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="T" And [L4]="Y","Proactive Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="T" And [L4]="T" And [L5]="Y","Redesign",
IIf([L1]="T" And [L2]="T" And [L3]="T" And [L4]="T" And [L5]="T","Run to Failure or Redesign",
"Incorrect Input"))))))

anw this code works fine.

the problem now, I just realized that the conditions aren't the same for H, S-E, and O.
H uses 'if conditions A', which is the same as the above, S-E uses 'if conditions B', which is slightly different from the above, and O uses 'if conditions C' which is also different.

if I was to input it as a single IIf code it would be too lengthy, haven't tried it yet though.
so how can I simplify it?
what I'm trying to achieve is a such.
If H="T", then if conditions A apply,
If H="Y" and S="T" then if conditions B apply,
If H="Y" and O="Y" the if conditions C apply.

edit:
got the multiple if-s working,

=IIf
([H]="T",
IIf(
[L1]="Y","On Condition Maintenance",
IIf([L1]="T" And [L2]="Y","Preventive Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="Y","Corrective Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="T" And [L4]="Y","Proactive Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="T" And [L4]="T" And [L5]="Y","Redesign",
IIf([L1]="T" And [L2]="T" And [L3]="T" And [L4]="T" And [L5]="T","Run to Failure or Redesign",
"Incorrect Input")))))),
IIf
([H]="Y" and [S]="Y" or [E]="Y",
IIf(
[L1]="Y","On Condition Maintenance",
IIf([L1]="T" And [L2]="Y","Preventive Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="Y","Corrective Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="T" And [L4]="Y","Proactive Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="T" And [L4]="T","Redesign",
"Incorrect Input"))))),
IIf
([H]="Y" and [S]="T" and [O]="Y" or [O]="T",
IIf(
[L1]="Y","On Condition Maintenance",
IIf([L1]="T" And [L2]="Y","Preventive Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="Y","Proactive Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="T","Run to Failure or Redesign",
"Incorrect Input")))))))

still, how could this be simplified?

thanks for your help

[RuralGuy]

Have you looked into a Usert Defined Function (UDF) in a standard module that uses a SELECT CASE structure? It would be more readable and maintainable than a nested If or IIF.

[shuto13]

thanks for the reply...
sadly no, I'm very new at access and VBA... I shall give it a try..
do you have any links where I can learn more about it?

edit:
sorry, could anyone make me an example of how this statement would look like in VB code?
I'm still trying to code it myself but an example would be a lot of help...

IIf(
[L1]="Y","On Condition Maintenance",
IIf([L1]="T" And [L2]="Y","Preventive Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="Y","Corrective Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="T" And [L4]="Y","Proactive Maintenance",
IIf([L1]="T" And [L2]="T" And [L3]="T" And [L4]="T" And [L5]="Y","Redesign",
IIf([L1]="T" And [L2]="T" And [L3]="T" And [L4]="T" And [L5]="T","Run to Failure or Redesign",
"Incorrect Input"))))))

thanks again

[shuto13]

right, so I've managed to rewrite the logic into a vba function

Function IfCondA()
Dim L1 As String
Dim L2 As String
Dim L3 As String
Dim L4 As String
Dim L5 As String

If L1 = Y Then
    IfCondA = "On Condition Maintenance"
ElseIf L1 = T And L2 = Y Then
    IfCondA = "Preventive Maintenance"
ElseIf L1 = T And L2 = T And L3 = Y Then
    IfCondA = "Corrective Maintenance"
ElseIf L1 = T And L2 = T And L3 = T And L4 = Y Then
    IfCondA = "Proactive Maintenance"
ElseIf L1 = T And L2 = T And L3 = T And L4 = T And L5 = Y Then
    IfCondA = "Desain Ulang"
ElseIf L1 = T And L2 = T And L3 = T And L4 = T And L5 = T Then
    IfCondA = "Run to Failure atau Wajib Dilakukan Desain Ulang"
Else
    IfCondA = "isian belum sesuai"
End If
    
End Function

the general idea is H, S, E, O (referring to the previous posts) dictate whether the conditional logic used is IfCondA, IfCondB or IfCondC. L1 through L5 dictate the outcome.
now, how do I use it in a query field or in a form field?
would it be as writing the below into the control source/expression of "Strategi" ? or am I missing something?

 
=IIf(H="T", IfCondA(), 
IIf(H="Y" and S="Y" or E="Y", IfCondB(),
IIf(H="Y" and S="T" and O="Y" or O="T", IfCondC()
)))

thanks again for your help...