Break Apart Name Field

[CatahoulaHound]

I have tried in vein to insert the code listed in the FAQ under "How Can I parse a Full Name into Last, First, MI" , into a module and run it. Can somebody please give me the code to do this. I have a field called "NAME" that has the full name typed in like "Austin John N." I have added 3 fields to my table-FirstName, LastName, MI. When I try to use the code in the FAQ, I get untold number of errors. I am using Access 97. Any help would be GREATLY appreciated.
Pat

 

[MichaelRed]

Lok ub "basSplit" in the FAQ's. Use it to get the individual 'words' in the [NAME] field. Place each 'word' in it's new field.

The overall task is daunting. When Developers use the single field for 'names', Users include a huge number of salutations and qualifiers (Mr. | Mrs | Sen | Rep | Ms | Capt | Esq | Jr | III | IV ... ). These will occur and MUST be dealt w/. You can either eliminate them or add additional field(s to accomodate them. Further - despite all efforts - different Users will enter the [NAME] in different formats. These also need to be resolved.

Even worse, many individuals prefer different forms of usage for the ir names. Many individuals use just the first Initial and their full middle name as their address. If your data base is to respect these nuances, You will need a few more fields - and some sophisticated processing to adaquetly capture the 'actual' names and to be able to address them in their perfered manner.

Altogether, It is a large and difficult effort, and probably not amenable to an all "coding" soloution. In the rather samll sample database where I was assigned this task, over 50% of the records required human intervention to 'parse' the single field information into the various prefered fields. I had sixfields for the "Name" (Prefix; FName; MNAme, LName, MadeNAme, and Suffix) and an additional field for the 'Prefered' address. The [PrefAddr] field was a code, designating the individuals 'prefered' form of address. It consisted of six "characters" for the use of each of the component fields
(F = Full, Abrv = Abreviatioon, I = Inital Only, O = Omit, H = Hyphenated ...).

As noted above, ther were as many exceptions as there were 'normal' names at the start of the sorting out process.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ChiTownDiva]

I had to do this once. Because I didn't know how to create a MID string, I had to go around Robin Hoods barn. Okay, here goes...Try this:

Add one more field, call it something like "BogusFirstName" and use an UPDATE querie--let's use your example of "Austin John N":

Last Name:

Update to: Left([Full Name], InStr(1, [Full Name]," &quot - 1)

In plain english, all of the letters left of the first space updates into Last Name field. Result: Austin

BogusFirstName:

Update to: Right([FullName], Len([FullName]) - InStr(1, [FullName], " &quot)

This puts everything to the right of the first space into the BogusFirstName field. Result: "John N"

First Name:

Update to: Left([BogusFirstName], InStr(1, [BogusFirstName]," &quot - 1)

We're repeating the first step: All of the letters left of the first space is updated to the First Name field. Result: John

MI:

Update to: Right([BogusFirstName], Len([BogusFirstName]) - InStr(1, [BogusFirstName], " &quot)

Now we're repeating the second step: All of the letters right of the first space is updated to the MI field. Result: N


Run the first query to split the Last Name and the BogusFirstName. Then run a second query to split the BogusFirstName and the MI.

I know there's some programmer that can probably write 2 lines of code to do the same thing, but for the rest of us, this works.

Woo Hoo, I hope this works for you!

 

[MichaelRed]

ChiTownDiva,
"I had to go around Robin Hoods barn."

Obviously you are 'into' minimalaization (e.g. understatement).
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ChiTownDiva]

I found a site that shows you how to split strings...I even figured out that MID split!

http://support.microsoft.com/support/kb/articles/q209/0/45.asp

 

[CaptainRon]

Here's some code I threw together if you want to eliminate a few steps! (Always backup the table before trying it) Open a new form and paste this into a command button. Change the names of the table and fields and run it. Hope this helps!

Dim dbs As Database
Dim rst As Recordset
Dim FirstBlank, SecondBlank As Integer
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT [Table1].* FROM Table1];&quot
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
FirstBlank = InStr(1, rst![Whole], " &quot
SecondBlank = InStr(FirstBlank + 1, rst![Whole], " &quot
rst.Edit
rst![Last] = Left(rst![Whole], FirstBlank - 1)
rst![First] = Mid(rst![Whole], FirstBlank + 1, SecondBlank)
rst![Middle] = Mid(rst![Whole], SecondBlank + 1, 1)
rst.Update
rst.MoveNext
Loop
rst.Close

 

[MichaelRed]

ChiTownDiva,

Good for you.

'___________________________________________________________
CaptainRon ,

Yes, BUT only for "well fomed" fields. You do NOT provide for any 'exception'. My experience suggests there will be al least as many exceptions as well formed entries.

P.S. The .MoveLast and .MoveFirst statements are entirely counter productive. All they do is force the traversal of the recordset. Twice!
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[CatahoulaHound]

ChiTownDiva:
Thanks for the code! Guess I forgot to mention that some of the names DON'T have middle initials. In that instance, I get no first name. How do I work around this?

TIA
Pat

 

[MichaelRed]

This is one of the exceptions, and where the 'others' fail. As I noted in the earlier post, you will encounter numerous exceptions. Many of these WILL require manual intervention. At least using [basSplit], you can get the string broken into individual words properly.

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over