Extracting first letter of last name in name field

[klp via AccessMonster.com]

I have a name field that has their first and last names. I need to extract
the first letter of their last name, is there an easy way of doing this?

thanks,
Kim P

 

[Douglas J. Steele]

Left([Surname], 1)

 

[Daryl S]

klp -

If your field always contains the first name, then a space, then the last
name, then this will work (substitute your names):

LastInitial = Mid([FullName],InStr(1,[FullName]," ")+1,1)

 

[klp via AccessMonster.com]

Fabulous, that worked. Thank you so much

klp -

If your field always contains the first name, then a space, then the last
name, then this will work (substitute your names):

LastInitial = Mid([FullName],InStr(1,[FullName]," ")+1,1)

I have a name field that has their first and last names. I need to extract
the first letter of their last name, is there an easy way of doing this?

thanks,
Kim P

 

[DaveT]

You can also work the problem right to left: search for first space from the
end of the name, then get the letter that follows.

Public Function LastInitial(yFullName)

Dim strFullName As String
Const ONE_SPACE = " "

Dim zPos As Long

On Error Resume Next

LastInitial = Null 'use "" is you want zero len string for no match

If Len(Nz(Trim(yFullName))) = 0 Then 'nothing to do
Exit Function

Else
strFullName = Trim(yFullName) 'strip any trailing spaces
End If

zPos = InStrRev(strFullName, ONE_SPACE)

If zPos > 0 Then
LastInitial = Mid(strFullName, zPos + 1, 1)
End If


End Function

 

[Larry Linson]

It will work nicely, as long as the first name is a single word. In the US,
and elsewhere also I think, many first names are two (sometimes even more)
words. For Bobby Joe Smith, it will return "J" as though it were the first
letter of the last name; for Mary Lou Southern, it will return "L".

Dealing with names is not a trivial thing, because there is such wide
variation in names.

Larry Linson
Microsoft Office Access MVP

Fabulous, that worked. Thank you so much

klp -

If your field always contains the first name, then a space, then the last
name, then this will work (substitute your names):

LastInitial = Mid([FullName],InStr(1,[FullName]," ")+1,1)

I have a name field that has their first and last names. I need to
extract
the first letter of their last name, is there an easy way of doing this?

thanks,
Kim P