CODEWITHSUNDEEP
ms-accessvbams-access-2010
VBA Challenged
VBA Challenged

Reputation: 23

How to have Multiple Conditions in an If/Then Statement

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

Upvotes: 2

Views: 30775

Answers (2)

JavaSheriff
JavaSheriff

Reputation: 7665

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:

Upvotes: 0

HansUp
HansUp

Reputation: 97101

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.

Upvotes: 3

Related Questions