DLookUp select secondary email if primary is empty or null-CodePudding

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

CodePudding user response：

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))

CodePudding user response：

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