John Spencer <(E-Mail Removed)> wrote in
news:(E-Mail Removed):
> If you are trying to trim OwnerFirstName then use
> Trim(OwnerFirstName & '')
>
> IIf(OwnerTitle is Null,'',OwnerTitle & ' ') &
> IIf(OwnerLastName is Null,'',Trim(Left(OwnerLastName,21)) & ', ')
> & Trim(OwnerFirstName & '') AS FullName
I can't remember who suggested it several years ago, but I use Null
propagation with the + operator to avoid having to use conditionals.
I don't know what OwnerTitle is referring to, so I'll temporarily
leave it out:
FullName: Mid(("12"+LastName) & (", "+FirstName), 3)
This appends 2 characters to any non-null filed, and then trims off
the leading 2 characters. If LastName is Null, it returns ",
FirstName", and then trims off the first two characters. If
FirstName is Null and LastName is not, it returns "12LastName" and
then trims off the first two characters. If both are not Null it
returns "12LastName, FirstName" and then trims off the first two
characters.
If the OwnerTitle is to be appended before that with a space in
between, you could do this:
(OwnerTitle+" ") & Mid(("12"+LastName) & (", "+FirstName), 3)
The result of this is that if OwnerTitle is Null, nothing is
returned, but if it's not Null, you get OwnerTitle plus a space that
is then Concatenated with the name. Now, if LastName and FirstName
can both be Null, you'll want to Trim() the result because there
could be a trailing space if OwnerTitle is NOT Null and the other
two fields are Null:
Trim((OwnerTitle+" ") & Mid(("12"+LastName) & (", "+FirstName),
3))
That should take care of all the possibilities without any need for
conditionals.
--
David W. Fenton
http://www.dfenton.com/
usenet at dfenton dot com
http://www.dfenton.com/DFA/
Similar Threads
