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

That's a great answer, Peter.

However, that begs the question as to why Art doesn't have separate fields for the parts of a customer name. Sounds like a design problem to me.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On May 2, 2014, at 12:22 AM, Peter Poppe <plpoppe@gmail.com> wrote:

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.