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

Jumat, 02 Mei 2014

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

Its a legacy issue. I inherited this database and am dealing with it the best I can. I am rewriting it but right now its what we have in production.

With Warm Regards,

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265 Ext. 130

Fax (605)964-1070

alorenzini@crhanetwork.org

"Only those who will risk going too far can possibly find out how far one can go."

On Friday, May 2, 2014 7: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

http://www.viescas.com/

(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

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.