title: Using IF to look at multiple fields to return a set value
body: <p>I have an access database that tracks our internal quality assurance checks for staff. They have 8 items we track them on and each of those items has 4 possible values:<br>
  Pass (1)<br>
  Non-Pass (2)<br>
  FYI (3)<br>
  NP&amp;FYI (4).<br>
  You can see these 4 set on the left in the column topped by "CPT". <img src="https://imgur.com/a/tZu361F" alt="screenshot">[1].  What I would like to have happen is if ANY of those 8 fields are FYI (ID 3) it sets the FYI checkbox to true. If ANY are Non-Pass (ID 2) It sets the passing checkboxes to false. If ANY of the fields are NP&amp;FYI (ID 4) I want the two checkboxes to be set to non-pass and FYI. The code I am trying is:</p>
  
  <pre><code>Private Sub Form_AfterUpdate()
  If (Me.Diagnosis And Me.CPT And Me.DOS And Me.UpDownCode And 
      Me.ChargeCorrections And Me.ChurnEsc And Me.Protocol And Me.BillServProv) 
      = 1 Then
      Me.FYI = 0
      Me.QAFail = -1
      End If
  If (Me.Diagnosis Or Me.CPT Or Me.DOS Or Me.UpDownCode Or 
      Me.ChargeCorrections Or Me.ChurnEsc Or Me.Protocol Or Me.BillServProv) = 2 
      Then
      Me.QAFail = 0
      End If
  If (Me.Diagnosis Or Me.CPT Or Me.DOS Or Me.UpDownCode Or 
      Me.ChargeCorrections Or Me.ChurnEsc Or Me.Protocol Or Me.BillServProv) = 3 
      Then
      Me.FYI = -1
      End If
  If (Me.Diagnosis Or Me.CPT Or Me.DOS Or Me.UpDownCode Or 
      Me.ChargeCorrections Or Me.ChurnEsc Or Me.Protocol Or Me.BillServProv) = 4 
      Then
      Me.FYI = -1
      Me.QAFail = 0
      End If
  End Sub
  </code></pre>
  
  <p>Unfortunately I'm getting inconsistent results. If I set field like CPT to non-pass then the FYI checkbox tics and the QAFail box does not clear. If I set other fields to other values I get all kinds of different results. I'm assuming its because there is confusion between the possible values of the fields being interpreted by the successive If statements. I thought about using CASE, but I couldn't figure out how to set the case value based on 8 different fields. I'm a novice at VBA, can anyone point me in the right direction?</p>
  
  <p>June7's alt code (works)</p>
  
  <pre><code>Dim strCodes As String
  With Me
  strCodes = Nz(.Diagnosis &amp; .CPT &amp; .DOS &amp; .UpDownCode &amp; _
      .ChargeCorrections &amp; .ChurnEsc &amp; .Protocol &amp; .BillServProv, "")
  End With
  If InStr(strCodes, 2) &gt; 0 Then
      Me.QAFail = 0
  ElseIf InStr(strCodes, 3) &gt; 0 Then
      Me.txtFYI = -1
  ElseIf InStr(strCodes, 4) &gt; 0 Then
      Me.txtFYI = -1
      Me.QAFail = 0
  ElseIf strCodes &lt;&gt; "" Then
      Me.txtFYI = 0
      Me.QAFail = -1
  End If
  End Sub
  </code></pre>