Need To Add another Field to this Query string

[airbus2]

Hello, I need help adding another field to this name string. I cant quite figure out where to add the MiddleName field to this:

Contact Name: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]))

Currently it reads in the unbound box as John Doe. I need it to read John David Doe, adding the middle name.

Any assistance is greatly appreciated.

 

[dhookom]

I would not want to do this in a query. There are too many nested IIf()s and as you found it's difficult to maintain. You may also want to use the same expression in another query or another application. If you don't know how to write a function, it's time to learn ;-)

Open a new blank module and paste in this code:

Public Function FMLCName(varFirst As Variant, _
        varLast As Variant, varMid As Variant, _
        varCo As Variant) As String
    If Len(varFirst & varLast & varMid & "") = 0 Then
        FMLCName = varCo & ""
     Else
        FMLCName = Trim(varFirst + " " & varMid + " " & varLast)
    End If
End Function

Then save the module as "modStringFunctions". You can now use this function in your query:

Contact Name: FMLCName([First Name], [Last Name],[MiddleName],[Company])

Duane
Hook'D on Access
MS Access MVP