Bob:
I know you've solved this with another approach already, but the reason
its
not working the way Douglas suggested is that you are calling the Nz
function
in the expression to return a zero-length string, so even if the
OwnerFirstName column is NULL the space won't be suppressed because there
is
never a NULL to propagate. If the Nz function call is removed it should
work:
(Left(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID]),1) + " ") & .................OwnerLastName......Code
Left(NULL,1) returns NULL, so Left(NULL,1) + " " will evaluate to NULL
because of the propagation of NULL, thus suppressing the redundant space
character. The advantage of this approach over calling the Trim function
is
that it will suppress other characters than spaces. Say you are returning
two values in the format 'Lastname, FirstName', if you use:
LastName & (", " + FirstName)
both the comma and the space will be suppressed if FirstName is NULL.
Ken Sheridan
Stafford, England
Bob said:
Thanks Douglas, but I am still getting a blank space before last name if
there is no first name e.g.:
|Douglas Steele
| Steele
Thanks for your help.......Bob
Douglas J. Steele said:
Take advantage of the fact that "A" + Null yields Null, while "A" &
Null
yields "A":
(Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) + " ") &
.................OwnerLastName......Code
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
I want to incorporate (" ") this into this part of the code because it
is
giving me a gap at the start when there is no First Name to display
and
Only Last Name!
Left(Nz(DLookUp("[OwnerFirstName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),1) & (" ") &
.................OwnerLastName......Code
Thanks in advance.........Bob Vance