How to have Multiple Conditions in an If/Then Statement

Question

I'm trying to write code in Access 2010 that when the [Validate] button is clicked, it will analyze multiple fields (8 in total) for a value (or no value) and then return a statement (or text) in another field ([AppStatus]) based on whether all of the 8 fields are entered or not. In other words, if any of the fields are null, the [AppStatus] field should populate with the default text of "RE PRE-QUAL". Below is where I started but I can't seem to figure out why it is not working.

Private Sub Validate_Click()
    If [PrimarySSN].Value = Null Then
    If [PropAddress].Value = Null Then
    If [PropCity].Value = Null Then
    If [PropState].Value = Null Then
    If [PropZipCode].Value = Null Then
    If [RequestedLoanAmount].Value = Null Then
    If [BorrowerIncome.Value] = Null Then
    If [EstHomeValue].Value = Null Then
          [AppStatus].Value = "RE PRE-QUAL"
    ElseIf [PrimarySSN].Value = Not Null Then
    ElseIf [PropAddress].Value = Not Null Then
    ElseIf [PropCity].Value = Not Null Then
    ElseIf [PropState].Value = Not Null Then
    ElseIf [PropZipCode].Value = Not Null Then
    ElseIf [RequestedLoanAmount].Value = Not Null Then
    ElseIf [BorrowerIncome].Value = Not Null Then
    ElseIf [EstHomeValue].Value = Not Null Then
          [AppStatus].Value = Null
    End If
    End If
    End If
    End If
    End If
    End If
    End If
End Sub

score 3 · Accepted Answer · edited May 23 '17 at 11:58

One of the reasons it's not working is because Nothing is ever equal to Null, not even another Null. Another issue is you can't use Not Null in VBA code. (Not Null is valid in Access SQL, but that doesn't help here.) In VBA, use IsNull() to check whether a value is Null.

You want "RE PRE-QUAL" as the value of AppStatus whenever one or more of those other 8 fields is Null (ie IsNull(fieldname.Value) = True). Otherwise, AppStatus will be Null. So you could do something like this ...

If IsNull(Me.PrimarySSN.Value) _
        Or IsNull(Me.PropAddress.Value) _
        ' additional Or conditions for each of next 5 fields
        Or IsNull(Me.EstHomeValue.Value) Then
    Me.AppStatus.Value = "RE PRE-QUAL"
Else
    Me.AppStatus.Value = Null
End If

However that would be a bit unwieldy when extended to all 8 fields.

As an alternative, you could start with a list of the control names, load them into an array, and use a For loop to walk the array checking whether each control value is Null. If any of them is Null, set AppStatus to "RE PRE-QUAL" and break out of the loop.

Dim astrFields() As String
Dim strFieldList As String
Dim varAppStatus As Variant
Dim varField As Variant
strFieldList = "PrimarySSN,PropAddress,PropCity,PropState,PropZipCode," & _
    "RequestedLoanAmount,BorrowerIncome,EstHomeValue"
astrFields = Split(strFieldList, ",")
varAppStatus = Null
For Each varField In astrFields
    'Debug.Print varField
    If IsNull(Me.Controls(varField).Value) = True Then
        varAppStatus = "RE PRE-QUAL"
        Exit For
    End If
Next
Me.AppStatus.Value = varAppStatus

Notice this approach could make maintenance easier. If you ever need to add or remove controls from the list of those which should be examined, or change any of their names, simply edit the strFieldList string.

That did it...Thank you HansUp! Is there a way to accomplish this without the clicking of a button and have it run automatically when the form closes? I tried applying the same code you provided above to the On Close function of the form but it returns the error "You can't assign a value to this object". When I debug it, its highlighting the Else Me.AppStatus.Value = Null portion of the code. — VBA Challenged, Sep 08 '15 at 16:15
You can't depend on controls being available during the form's *On Close* event. Consider *On Unload* instead. Or put the code in a separate procedure and call that from the *After Update* event of each of those fields which determine `AppStatus.Value`. — HansUp, Sep 08 '15 at 16:44

score 0 · Answer 2 · edited May 23 '17 at 12:06

I think that this is what you need:

If [PrimarySSN].Value = Null Or [PropAddress].Value = Null Or [PropCity].Value = Null Or [PropState].Value = Null Or [PropZipCode].Value = Null Or [RequestedLoanAmount].Value = Null Or [BorrowerIncome.Value] = Null Or [EstHomeValue].Value = Null Then
      [AppStatus].Value = "RE PRE-QUAL"
Else
      [AppStatus].Value = Null    
End If

And also consider this:

How to have Multiple Conditions in an If/Then Statement

2 Answers2