DLookUp select secondary email if primary is empty or null

Question

I have a form that is selecting emails based on companies that have expired insurances. Some companies have both a Business and personal emails whilst others have one or the other. I am using the DLookUp function to select the email based on Suppliers meeting the filter criteria. Is there a way for me to select the BusinessEmail but if they don't have one it selects the PersonalEmail instead?

Currently I have started with =DLookUp("BusinessEmail","tblContacts","Supplier ID = " & [txtSupplierID] and then went to use an IIf statement in it but I was lost as to how it would then select the other email or if it would work that way.

contacts sample data

Answer 1

The DLookup() function returns null if the value was not found, so you can make use of the Nz() function to check for the personal email, if the business email is null.

Since we assign the return value to a string and strings cannot hold null values, we make use of the Nz() function again to return an empty string, if the personal email doesn't exists too (to avoid the error).

Dim criteria As String
criteria = "[Supplier ID] = " & [txtSupplierID]

Dim email As String
email = Nz(DLookup("BusinessEmail", "tblContacts", criteria), Nz(DLookup("PersonalEmail", "tblContacts", criteria), vbNullString))

Edit:

---

Adding a second rule to the DLookup to exclude null values, should work. See a helper function to get the email through an output parameter and report success/failure:

Private Function TryGetEmail(ByVal supplierId As Long, ByRef outEmail As String) As Boolean

    Dim retValue As Variant
    
    'try to get business
    retValue = DLookup("BusinessEmail", "tblContacts", "[Supplier ID] = " & supplierId & " AND BusinessEmail Is Not Null")
    If Not IsNull(retValue) Then
        outEmail = retValue
        TryGetEmail = True
        Exit Function
    End If
    
    'try to get personal
    retValue = DLookup("PersonalEmail", "tblContacts", "[Supplier ID] = " & supplierId & " AND PersonalEmail Is Not Null")
    If Not IsNull(retValue) Then
        outEmail = retValue
        TryGetEmail = True
        Exit Function
    End If
    
    'none found
    outEmail = vbNullString
    TryGetEmail = False
    
End Function

---

To call it:

Sub T()

    Dim supplierId As Long
    Dim email As String
    
    supplierId = 19
    
    If Not TryGetEmail(supplierId, email) Then
        MsgBox "No email found"
        Exit Sub
    End If
    
    'email now holds one of the two emails

End Sub

Answer 2

First, change the field "Supplier ID" to "SupplierID", it will make your development easier.

Here are some code using both Dlookup and IIf, putting the result in a textbox named txtEmail:

Private Sub getEmail()
    txtEmail = DLookup("Iif(Isnull(BusinessEmail),PersonalEmail,BusinessEmail)", "tblContacts", "SupplierID = " & txtSupplierID)
End Sub

or this:

Private Sub getEmail()
    Dim Email As Variant
    
    Email = DLookup("BusinessEmail", "tblContacts", "SupplierID = " & txtSupplierID)
    If IsNull(Email) Then Email = DLookup("PersonalEmail", "tblContacts", "SupplierID = " & txtSupplierID)
    
    txtEmail = Email
End Function