Change Trim from LastName to FirstName

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

I am tring to change the Trim From Last Name to FirstName
with out much success!
Dim recOwnersInfo As New ADODB.Recordset



recOwnersInfo.Open "SELECT
OwnerID,IIf(isnull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & '
') & " _
&
"IIf(isnull(tblOwnerInfo.OwnerLastName),'',trim(Left(tblOwnerInfo.OwnerLastName,21))
& ', ') & " _
&
"IIf(isnull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName) AS
Name, " _
& "OwnerAddress FROM tblOwnerInfo WHERE OwnerID=" &
val(recHorseOwners.Fields("OwnerID")), _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic


If recOwnersInfo.EOF = True And recOwnersInfo.BOF = True
Then
recOwnersInfo.Close
Set recOwnersInfo = Nothing
Else
 
Perhaps you want an expression that looks like the following:

IIf(OwnerTitle is Null,'',OwnerTitle & ' ') &
Trim(IIf(OwnerLastName is Null,'',Left(OwnerLastName,21) & ', ') &
IIf(OwnerFirstName is Null,'',OwnerFirstName)) AS FullName

Or perhaps
IIf(OwnerTitle is Null,'',OwnerTitle & ' ') &
Trim(IIf(OwnerLastName is Null,'',Trim(Left(OwnerLastName,21)) & ', ') &
IIf(OwnerFirstName is Null,'',OwnerFirstName)) AS FullName


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
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

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
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
Click to expand...

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.
 
John Spencer said:
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

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
 
Back
Top