Adding a Blank between Names!

[Bob]

At the moment my code is giving me this result from
Mr John Smith ....I am getting
Mr JSmith.....And I wanted
Mr J Smith

=IIf(DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" & [tbOwnerID])="" Or
IsNull(DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" &
[tbOwnerID]) & " ") &
Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) &
IIf(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" & [tbOwnerID])="" Or
IsNull(DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerLastName","tblOwnerInfo","OwnerID=" &
[tbOwnerID]))


Thanks in advance.........Bob Vance

 

[Allen Browne]

Try something like this:

=Trim(DLookup("[OwnerTitle] + "" "" & [OwnerFirstName] + "" "" &
[OwnerLastName]", "tblOwnerInfo", "OwnerID = " & Nz([tbOwnerID],0)))

1. The first argument can be an expression that combines several fields. A
single DLookup() will be more efficient.

2. If the quotes within quotes don't make sense, see:
http://allenbrowne.com/casu-17.html

3. There are 2 concatenation operators which are slightly different:
"Z" + Null generates Null
whereas:
"Z" & Null generates "Z".

4. The Nz() in the 3rd argument avoids the error if tbOwnerID is null. It
would error if the 3rd argument evaluated to just:
OwnerID =

5. The Trim() is another technique for dropping any leading or trailing
spaces.