ACCESS FEED: Re: [MS_AccessPros] Displaying a part of a string from a field

Kamis, 01 Mei 2014

Re: [MS_AccessPros] Displaying a part of a string from a field

Probably something like this may be needed depending on your data.

Public Function GetLastName(FullName As Variant) As String

Dim aNames() As String

If Not IsNull(FullName) Then

aNames = Split(FullName, " ")

GetLastName = aNames(UBound(aNames))

End If

Select Case GetLastName

Case "Jr", "Jr.", "Sr.", "Sr", "II", "III", "IV"

GetLastName = aNames(UBound(aNames) - 1)

End Select

End Function

Select GetLastName([CustomerName])...

Handles stuff like "Billy Jean King", "Harry Connick Jr.", "Robert Griffin III". Also handles names in format of Mr. John Smith, or First M. Last

On Thu, May 1, 2014 at 5:46 PM, Liz Ravenwood <liz_ravenwood@beaerospace.com> wrote:

Oops. Intspaceloc = instr(CustomerName," ")

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
Sent: Thursday, May 01, 2014 2:45 PM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] Displaying a part of a string from a field

Something like

Intspaceloc = instr(CustomerName)

strLname = mid(CustomerName,intSpaceLoc,len(CustomerName-intSpaceLoc))

?

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of dbalorenzini@yahoo.com
Sent: Thursday, May 01, 2014 1:55 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Displaying a part of a string from a field

I have a table with a field call CustomerName:

Joe Blow

Jane Doe

Mark Smith

What I need is to view the last name by itself but I am not sure how to do it.

Select CustomerName from Customer.

Any Ideas?

Thank you

Arthur Lorenzini

Sioux Falls, SD

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.