lots of "and"s in if statement

[genghis82]

Hi

I have code in vba that I want to execute only if a number of criteria are met.

Private Sub Form_Current()
'displays the file status and the TA status for any study
On Error GoTo PROC_ERR

Dim SFStatus As String

Dim SFStatus1 As String
Dim SFStatus2 As String
Dim SFStatus3 As String

Dim TAStatus As String

Dim TAStatus1 As String
Dim TAStatus2 As String

Dim CommitStatus As String

Dim CommitStatus1 As String
Dim CommitStatus2 As String

Dim MemoStatus As String

Dim MemoStatus1 As String
Dim MemoStatus2 As String
Dim MemoStatus3 As String

Dim SentStatus As String

Dim SentStatus1 As String
Dim SentStatus2 As String
Dim SentStatus3 As String


'declare all variables then show equivelence for SF Status. 

    SFStatus1 = "NOT ASSIGNED"
    SFStatus2 = "IN PROGRESS"
    SFStatus3 = "COMPLETED"

'show equivelence for TA status
    TAStatus1 = "NOT ASSIGNED"
    TAStatus2 = "ASSIGNED"

'for Commit Date
    CommitStatus1 = "NO COMMIT DATE"
    CommitStatus2 = "A COMMIT DATE"

'and again for GMO status
    MemoStatus1 = "a memo is NOT REQUIRED."
    MemoStatus2 = "a memo is still REQUIRED."
    MemoStatus3 = "a memo has been PROVIDED."
    
'finally for SentToLab Status
    SentStatus1 = "HAS NOT been sent"
    SentStatus2 = "HAS sent"
    
'arguments
'if a CS coordinator has not been assigned then the Study file is "not assigned"
'if a CS coordinator has been assigned but the file is not complete then "in progress"
'if the file is both assigned and complete, then "completed"

        If IsNull(Me.CS_coordinator) Then
            SFStatus = SFStatus1
            
            ElseIf Not IsNull(Me.CS_coordinator) And Completed = False Then
                SFStatus = SFStatus2

           Else
                SFStatus = SFStatus3

       End If
       
'if the TA receipt date is null then the TA status "TA unassigned"

        If IsNull(Me.TA_receipt_date) Then
                TAStatus = TAStatus1
                   
'otherwise the TA is "assigned"

            ElseIf Not IsNull(Me.TA_receipt_date) Then
                TAStatus = TAStatus2

        End If


        
'if the commit date is null then notify the user that a commmit date has not been provided

        If IsNull(Me.Client_Commit_Date) Then
             CommitStatus = CommitStatus1
             
'if the commit date has been provided then notify user as such
            ElseIf Not IsNull(Me.Client_Commit_Date) Then
                CommitStatus = CommitStatus2
        
        End If
        
'if the GMO is not required and there is not GMO number then GMO "not required"

    If (Me.GMO_req) = "No" And IsNull(Me.GMO_) Then
        GMOStatus = GMOStatus1
        
'if the GMO is required but the GMO number has not been provided then notify user as such

     ElseIf (Me.GMO_req) = "Yes" And IsNull(Me.GMO_) Then
        GMOStatus = GMOStatus2
        
'the last possibility should be that the GMO is required and the number has been provided. If so then GMO "provided"
     
     ElseIf (Me.GMO_req) = "Yes" And Not IsNull(Me.GMO_) Then
        GMOStatus = GMOStatus3
   
   End If
   
'gosh! finally for the sent to lab status and hide the box if no value
    
    If IsNull(Me.Sent) Then
    
        SentStatus = SentToLabStatus1
        Date.Visible = False
    
    Else
        SentStatus = SentStatus2
        Date.Visible = True
        
    End If
        
      
'if the memo required field is set to no then dont show field

    If Me.memo_req = "No" Then
    Me.memo.Visible = False
    Else
        Me.memo.Visible = True
        
    End If
    
'hide the date completed box if it is null

            If IsNull(Me.DateCompleted) Then
                    DateCompleted.Visible = False
                    Else
                    Me.DateCompleted.Visible = True
                    
             End If


    
'display complete status of study file to user in the lblmessage label

lblmessage.Caption = "This STUDY FILE is " & SFStatus & " and the TEST ARTICLE is " & TAStatus & ". " & CommitStatus & " has been provided and " & MemoStatus & " The SF " & SentStatus 

PROC_EXIT:
Exit Sub

PROC_ERR:
    Call ShowError("PubFunErrorHandling", "frmStudyFileStatusSD, Form_Current", err.Number, err.Description)
    GoTo PROC_EXIT

End Sub

This is the full code I have and although it does appear to work, is there a cleaner way of handling this? I thought a case select would work, but I couldn't get it to work as its not one criteria and many actions. Rather it is many criteria to produce one action.

I am looking to add an extra line that would be something like

If SFStatus=SFStatus3 and TAStatus=TAStatus2 and SentStatus = SentStatus2 and MemoStatus=MemoStatus1 or MemoStatus2

But this won't work and seems incredibly messy. I'm really going around in circles here - please help!!

Thanks

 

[RV]

What puzzles me a bit, to be frankly honest, is that normally you basically can accomplish what you're after by using a (as in one) query.
That is, there is a dependency between the different statuses, for instance, status2 can not start if status1 has not been completed.
Are you storing your statuses in one table or do you store a separate row per status, as you should?

RV

 

[genghis82]

Hi

Not too sure I understand what your saying. I dont actually store these statuses anywhere as I only use it to output on to a label message.

When someone searches for the Study Number they are then shown a message screen showing the full status which is dependent on a number of fields.

Hope this answers the question ????

 

[The_Doc_Man]

It occurs to me that you may have thought of this in a non-state design "mind set" when you should have been thinking "stateful" at the time.

If the state is unique AND the sequence is predictable, consider a state variable and a transition case.

E.g. States are one of A,B,C,D,E - and rules exist to control next state from current state - and A is initial and E is terminal.

Select STATEVAR
  Case A
    if criteria1 then STATEVAR = B
    if criteria2 then STATEVAR = C
    etc.
  Case B
    etc.

  End Select

Then define the transitions on subsets of originating states. If you have not already done so, look up (GOOGLE SEARCH) "Finite State Automata" and "Automata Theory" - might also spell that "Automaton"

Using "stateful" transitions make it easier to program things such as you describe because it is a form of "divide and conquer" programming. Reduce the size of the parts. Eventually you get the whole problem programmed. Note that inside the individual Case "code chunks" it does not matter if you look at data other than the main state code value.

If the states are related such that A,B,C,D,E are always forward-only transitions, you start saving code in huge chunks the farther in the list you get. Like, in theory you have four transitions from state A (to B,C,D,E) but by the time you get to state C, you have only two possible transitions left, and for state D the only problem is "to E or not to E, that is the question...."

I know it might mean a little extra work up front, but if you can define the so-called "transition rules" for changes from one state to another, you do yourself a MASSIVE favor in terms of organizing your program. AND in terms of maintaining it, since each code chunk is now sub-divided to a more limited scope of options.

Hope this has been helpful. If not, chalk it up to me misunderstanding what you were trying to do.

 

[Pat Hartman]

1. There is no purpose that I can determine for all the variable definitions and setting. The code would be more understandable if you simply put the error messages directly in the code where they occur. That would also eliminate the need for most of the comments since the error messages should explain what is going on. Also, if you remove the bulk of the comments, you can tighten up the spacing and the code will look neater and be more comprehensible since you have a better shot at seeing an entire expression without having to scroll down the page.
2. You repeat the IsNull() tests unnecessarilly. Most of the ElseIf's should be removed. The statements should be:

If IsNull(....) Then
    "what to do if null"
Else
    "what to do if not null"
End If

when there are dependent tests, insert them in the appropriate path of the If statement.

If IsNull(....) Then
    "what to do if null"
Else
    If IsNull(...) Then
        "what to do if A not null but B is null"
    Else
        "what to do if both A and B are not null"
    End If
End If

 

[genghis82]

Hi Pat,

Thanks a lot for this. Your right in that my logic on this was flawed. I've rewritten the code as you suggested and now have :

Private Sub Form_Current()
'displays the study file status and the TA status for any study
On Error GoTo PROC_ERR

Dim SFStatus As String
Dim TAStatus As String
Dim CommitStatus As String
Dim GMOStatus As String
Dim SentToLabStatus As String
    
'define SF Status

        If IsNull(Me.CS_coordinator) Then
            SFStatus = "Not Assigned"
            
        Else
            If Completed = False Then
                SFStatus = "In Progress"
                DateCompleted.Visible = False
                
            Else
                SFStatus = "Completed"
        End If
        End If
               
'define TA Status

        If IsNull(Me.TA_receipt_date) Then
                TAStatus = "Not Assigned"

        Else
                TAStatus = "Assigned"

        End If
       
'define Commit Date Status

        If IsNull(Me.Client_Commit_Date) Then
             CommitStatus = "No Commit Date"
        
        Else
             CommitStatus = "A Commit Date"
             
        End If
        
'define GMO Status

    If (Me.GMO_req) = "No" Then
        GMOStatus = "a GMO memo is NOT required."
        Me.GMO_.Visible = False
        
     Else
     
        If IsNull(Me.GMO_) Then
            GMOStatus = "a GMO memo is still required."
    
        Else
        
            GMOStatus = "a GMO memo has been provided."
        
        End If
        
    End If
    
'sent to lab status
    
    If SentToLab = False Then
        SentToLabStatus = "Has Not"
        DateToLab.Visible = False
        
    Else
        SentToLabStatus = "Has"
        
    End If
    
'display complete status of study file to user in the lblmessage label

lblmessage.Caption = "This Study File is " & SFStatus & " and the Test Article is " & TAStatus & ". " & CommitStatus & " has been provided and " & GMOStatus & " The SF " & SentToLabStatus & " been passed to the SD."

PROC_EXIT:
Exit Sub

PROC_ERR:
    Call ShowError("PubFunErrorHandling", "frmStudyFileStatusSD, Form_Current", err.Number, err.Description)
    GoTo PROC_EXIT

End Sub

Which seems much more readable to me.

Thanks a lot

 

[genghis82]

It occurs to me that you may have thought of this in a non-state design "mind set" when you should have been thinking "stateful" at the time.

Hi Doc

Thanks for this suggestion. I had never heard of this before so it was interesting to read up on. The only problem with the code I was trying to write was that it was on 5 mutually exclusive if statements. However I was trying to use this for the Sent Status as this could be written as a list

Dim SentStatus as string

Select SentStatus
    Case A
      if completed = True then SentStatus = B
      if completed = False then SentStatus = "not sent"
    Case B
      if isnull (me.gmo_req) then SentStatus= D
      if not isnull (me.gmo_req) then SentStatus= C
    Case C
      if isnull (me.gmo_) then SentStatus = "not sent"
      if not is null (me.gmo_) then SentStatus = D
    Case D
      if sent = True then SentStatus = "sent"
      if sent = False then SentStatus = "ready for collection"

The problem I am having is that I would need to declare the Case A-D variables and I am stumped on how I would use this?? I need more coffee!!

 

[Steve R.]

Using the CASE statement as suggested by The_Doc_Man would be preferred.

sentstatus =0
if situation "A" then sentstatus =1 <=Note these are all one liners and simple if statements
if situation "B" then sentstatus =2
if situation "C" then sentstatus =3

select sentstatus
  case 1
        Enter code for what to do. Minimize the use of "IF" statements.
  case 2
        Enter code for what to do.
  case 3
        Enter code for what to do
  Case Else
        Unexpected error. Enter code for what to do. Technically CASE ELSE should never occur.
End Select

 

[Pat Hartman]

You still have way too much white space in the wrong places. You also need to set both sides of visible or the form will "remember" the setting from th previous record. I added two lines of code to handle it. Try this:

Private Sub Form_Current()
'displays the study file status and the TA status for any study
On Error GoTo PROC_ERR

Dim SFStatus As String
Dim TAStatus As String
Dim CommitStatus As String
Dim GMOStatus As String
Dim SentToLabStatus As String
    
'define SF Status
        DateCompleted.Visible = True

        If IsNull(Me.CS_coordinator) Then
            SFStatus = "Not Assigned"            
        Else
            If Completed = False Then
                SFStatus = "In Progress"
                DateCompleted.Visible = False                
            Else
                SFStatus = "Completed"
           End If
        End If
               
'define TA Status

        If IsNull(Me.TA_receipt_date) Then
                TAStatus = "Not Assigned"
        Else
                TAStatus = "Assigned"
        End If
       
'define Commit Date Status

        If IsNull(Me.Client_Commit_Date) Then
             CommitStatus = "No Commit Date"        
        Else
             CommitStatus = "A Commit Date"             
        End If
        
'define GMO Status
    Me.GMO_.Visible = True

    If (Me.GMO_req) = "No" Then
        GMOStatus = "a GMO memo is NOT required."
        Me.GMO_.Visible = False        
     Else     
        If IsNull(Me.GMO_) Then
            GMOStatus = "a GMO memo is still required."    
        Else        
            GMOStatus = "a GMO memo has been provided."        
        End If        
    End If
    
'sent to lab status
    
    If SentToLab = False Then
        SentToLabStatus = "Has Not"
        DateToLab.Visible = False        
    Else
        SentToLabStatus = "Has"        
    End If
    
'display complete status of study file to user in the lblmessage label

lblmessage.Caption = "This Study File is " & SFStatus & " and the Test Article is " & TAStatus & ". " & CommitStatus & " has been provided and " & GMOStatus & " The SF " & SentToLabStatus & " been passed to the SD."

PROC_EXIT:
Exit Sub

PROC_ERR:
    Call ShowError("PubFunErrorHandling", "frmStudyFileStatusSD, Form_Current", err.Number, err.Description)
    GoTo PROC_EXIT

End Sub

Which seems much more readable to me.