Results 1 to 4 of 4
  1. #1
    shuto13 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2012
    Posts
    10

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

    Hello everyone,


    sorry if I'm in the wrong room...

    here is what I have

    Click image for larger version. 

Name:	lta decision.jpg 
Views:	25 
Size:	94.9 KB 
ID:	8646

    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
    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,
    Code:
    =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

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    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.

  3. #3
    shuto13 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2012
    Posts
    10
    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...
    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"))))))
    thanks again
    Last edited by shuto13; 08-01-2012 at 12:03 AM.

  4. #4
    shuto13 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2012
    Posts
    10
    right, so I've managed to rewrite the logic into a vba function
    Code:
    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?
    Code:
     
    =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...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 04-13-2012, 12:25 PM
  2. Replies: 16
    Last Post: 04-11-2012, 10:56 AM
  3. Replies: 1
    Last Post: 03-03-2012, 10:17 PM
  4. Replies: 1
    Last Post: 02-20-2012, 01:59 PM
  5. Replies: 3
    Last Post: 10-03-2011, 02:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums