Jumat, 02 Mei 2014

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

 

Here are some other features to handle things like
"john Smith " This would fail because of the space
"John   Smith" This would fail because of the double space

Public Function GetLastName(FullName As Variant) As String
  Dim aNames() As String
  'since function is used in a query always make the argument a variant
  'and then check for nulls
  If Not IsNull(FullName) Then
    'In case there is a space after the lastname
    FullName = Trim(FullName)
    'Remove any double blanks:  John   Smith
    Do
      FullName = Replace(FullName, "  ", " ")
    Loop Until InStr(FullName, "  ") = 0
    aNames = Split(FullName, " ")
    GetLastName = aNames(UBound(aNames))
  End If
  'Remove special cases
  Select Case GetLastName
   Case "Jr", "Jr.", "Sr.", "Sr", "II", "III", "IV"
    GetLastName = aNames(UBound(aNames) - 1)
  End Select
End Function

Good news this also handles "Bono, Madonna, Sting" as the last name, But not sure if those are first or last names.


On Fri, May 2, 2014 at 10:33 AM, Peter Poppe <plpoppe@gmail.com> wrote:
Not necessarily, but I guess about 99% of the time it does, If option compared datatabase is used then it depends on the value of "new database sort order" in the general options.  I guess if you have this set to something crazy like "Japanese Radical Stroke Count" (who comes up with these names), it may not work. Actually I tested Japanese Radical Stroke Count and it was case insensitive at least for lower ASCII.


On Fri, May 2, 2014 at 8:24 AM, John Viescas <JohnV@msn.com> wrote:
 

John-


If the code has the default Option Compare Database, then all string comparisons in Access are case insensitive.

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 2:07 PM, lancucki <lancucki@magma.ca> wrote:

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 (11)

Did you check out automatic photo albums in Yahoo Groups?
When you send mail to your Yahoo Group with photos attached, a photo album with attached photos is automatically created. When your group members reply to the email with photo attachments, those photos are added to the 'photomatic' albums automatically!

.

__,_._,___

Tidak ada komentar:

Posting Komentar