Let ( [
first = VendorName & " " & VendorSurname ;
second = VendorName2 & " " & VendorSurname2
Substitute ( List ( first ; second ) ; Char(10) ; " & " )
You could apply that method to the two pairs of name themselves first, then join the results of those calculations.
On another note: consider your database design.
The only place where you should have two name fields is in a table where you store entities that have names. But then you'd only have one set of name fields, because one record = one entity instance.
The place where you could have two names is in a related table. But there you would a) use IDs, and b) if you could have two (or more) named entities, you'd probably create a join or a child table.
Assuming you only have to check once (i.e. the two vendor2 fields go together—if you have one you'll have both) try:
Vendor Name & " " &Vendor Surname & If ( IsEmpty ( Vendor fisrtname2 ; "" ; " & " & Vendor firstname2 & " " & Vendor Surname2 )
This is exactly what I wanted - thank you!!