Yeah, especially because Allen forgot to say what LogError in his ParseWord function looks like.
Giorgio
On June 5, 2020 at 5:52 PM 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_HandlerEnd 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
Tidak ada komentar:
Posting Komentar