One slight tweak…
The comparison should be done against an Uppercase (or Lowercase) version of the string to catch JR and jr and the obvious typo jR
John… Visio MVP
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Friday, May 02, 2014 1:35 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
(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.
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar