Re: [MSAccessProfessionals] Split full name into first and last fields

That work perfectly. I really appreciate the help.

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

On Friday, June 5, 2020, 10:53:15 AM CDT, Duane Hookom <duanehookom@hotmail.com> wrote:

Hi Art,

I would use Allen's code with a couple small updates toward the end. His code relies on an error handler from some other page on his site. I would also trim all the spaces from the end of your values. Paste Allen's code in a new, blank module, make the below changes, compile, and save as "modStringConversions".

Substitute or change the last lines of his code like:

    '*************************************

    'Return the result, or a null if it is a zero-length string.

    '*************************************

    If strResult <> vbNullString Then

        ParseWord = Trim(strResult)   '### modified by Duane

    Else

        ParseWord = Null

    End If

Exit_Handler:

    Exit Function

Err_Handler:

    MsgBox Err.Number & ": " & Err.Description & " ParseWord()"   '### modified by Duane

    Resume Exit_Handler

End Function

Then you can use the function anywhere in your application like:

SELECT tblTenants.TENANTNAME, ParseWord([TENANTNAME],1,", ") AS LastName, ParseWord([TENANTNAME],2,", ") AS FirstName

FROM tblTenants;

The only possible issue I see from your sample is with the Active PD records. 

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Art Lorenzini via groups.io <dbalorenzini=yahoo.com@groups.io>
Sent: Friday, June 5, 2020 10:13 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Split full name into first and last fields

 

The full name is coming out of our Financial Software so I don't control it. That is why I want to split it apart so I have better control over it. 

Sample data:
TENANTNAME
Iron Hawk, Manny                                                
Iron Hawk, Tallie                                               
Iron Bird, Inez                                                 
Iron Lightning, Robinn                                          
Iron Lightning, Veronica                                        
Iron Hawk, Margo                                                
Iron Hawk, Matthew                                              
Iron Wing, Roxanne                                              
Iron Hawk, Wayne                                                
Iron Wing, Chauncina                                            
Iron Wing, Barbara                                              
Iron Wing, Carrie                                               
Isna, Roderick                                                  
Active PD, Alan Jamerson                                        
Active PD, Gary Janise                                          
Janis, Henrietta                                                
Janis, Jesse                                                    
Janis, Lucinda                                                  
Janis, Marletta                                                 
Janis, Patience                                                 
Janis, Vanessa                                                  
Jeffries, Jenny                                                 
Jeffries, Lowan                                                 
Jeffries, Milton                                                
Jeffries, Wade                                                  
Jensen, Diana                                                   
Jeunesse, Madeline                                              
Jewett, Cecelia                                                 
Jewett, Cyril                                                   
Jewett, Francis/Arllen                                          
Jewett, Frank                                                   
Jewett, Gregory                                                 
Jewett, Iyonne                                                  
Jewett, Louis                                                   
Jewett, Nina                                                    
Jewett, Samantha                                                
Johnson, Adell                                                  
Johns, Todd                                                     
Johnson, Tyllia                                                 
Johnson, Vivian                                                 
Jones, Neccia E.                                                
Kasto, Naomi                                                    
Keckler, Arliss