I have a field called name

format is: Lastname, FirstName Middlename

I want to separate these three pieces of data into columns:
Last Name First Name Middle Initial


Notice I only want the first letter of the middlename. How should I go about doing this. I'm thinking that I need to do something similar to an InStr to find the comma then read before it, but I am not familiar with the process or syntax for SQL. I am currently in SQL view but any advice would be appreciated.

Thanks!

strColumns = Split(strField, ",")
That should give you strColumns (0) the First half before the Comma and strColumns (1) the last half after the comma.
Then you would need to focus on "FirstName Middlename". Names can be tricky so a Split(strField, " ") could fail to return accurate results on this portion.

I have a field called name

format is: Lastname, FirstName Middlename

I want to separate these three pieces of data into columns:
Last Name First Name Middle Initial


Notice I only want the first letter of the middlename. How should I go about doing this. I'm thinking that I need to do something similar to an InStr to find the comma then read before it, but I am not familiar with the process or syntax for SQL. I am currently in SQL view but any advice would be appreciated.

Thanks!


Take a good look at the following functions:

Instr(StartPosition, BaseString, TargetString)
InstrRev(StartPosition, BaseString, TargetString)
Mid(BaseString, StartPosition, NumberofChars)
Left(BaseString, NumberofChars)
Right(BaseString, NumberofChars)
Using the above Functions:

You can ise Instr() to find the Comma.
You can ise InstrRev() to find the Space.
You can use Mid() or Left() to locate the LastName.
You can use Mid() to locate the FirstName.
You can use Mid() or Right() to locate the MiddleName, and then use Left() to get the first Character for the Initial.
Notice that I have not provided any code. I have instead provided all of the topols that you will need. Give it a try and post back if you have any further questions.

Thanks, I am somewhat familiar with the Left and Right commands. I am cautious to just try it. I have a database of 980,000 entries with several fields. I just dont want to screw up my data or wait to make a new copy of this db each time I guess and mess up.

I am not sure how to use the instr command WITH the left and right commands.

Hi -

Take a look at Allen Browne's ParseWord() function at http://allenbrowne.com/func-10.html. It can take out a lot of the hard work.

HTH - Bob

Thanks, I am somewhat familiar with the Left and Right commands. I am cautious to just try it. I have a database of 980,000 entries with several fields. I just dont want to screw up my data or wait to make a new copy of this db each time I guess and mess up.

I am not sure how to use the instr command WITH the left and right commands.

You can calculate the NumberofChars that the Left() Function will need by locating the position of the Comma in the BaseString (using the Instr() Function), and subtracting 1.

You can calculate the NumberofChars that the Right() Function will need by locating the position of the Space in the BaseString (using the InstrRev() Function), and subtracting that position from the Total Length of the string.

Ok, I am trying something along these lines:

SET last_name = substr(teachername,1,instr(teachername, ',') -1)

What does access use instead of the Substring command to do this procedure?

Ok, I am trying something along these lines:

SET last_name = substr(teachername,1,instr(teachername, ',') -1)

What does access use instead of the Substring command to do this procedure?

The structure seems compatible with Mid(), so try using that Function.

Thanks, I am somewhat familiar with the Left and Right commands. I am cautious to just try it. I have a database of 980,000 entries with several fields. I just dont want to screw up my data or wait to make a new copy of this db each time I guess and mess up.

I am not sure how to use the instr command WITH the left and right commands.

So
1 Take a backup before you do anything
2 create a small test db covering all possibilities and when you have things working move to the live DB, but still backup

Brian

Hi -

Allen Browne's ParseWord() function referred to in Post #5 can, with very little setup, return something like this.

The sub to be called:

Public Sub FixName(strName As String)
Dim i As Integer
Dim strFld As String

For i = 1 To 3
strFld = "Field" & i
Debug.Print strFld & " " & ParseWord(strName, i, IIf(i = 1, ",", " "), True)
Next i
End Sub

and when called from the debug (immediate) window:

call fixname("Smith, John Charles")

Field1 Smith
Field2 John
Field3 Charles

You'd just need to modify the calling routine to meet your needs.

Bob

Expanding on my previous post:

If you had a table that looked like this:

strFull Field1 Field2 Field3
Doe, John Q.
Smith, Sally Ann
Jones, Robert

This simple Update query:

UPDATE tblParseTest SET
tblParseTest.Field1 = ParseWord([strFull],1,",",True)
, tblParseTest.Field2 = ParseWord([strFull],2," ",True)
, tblParseTest.Field3 = ParseWord([strFull],3," ",True);

Would produce this:
strFull Field1 Field2 Field3
Doe, John Q. Doe John Q.
Smith, Sally Ann Smith Sally Ann
Jones, Robert Jones Robert

Suggest you give it a try.

Best wishes - Bob

Suggest you give it a try.

I wholeheartedly agree with Raskew here! Don't try to reinvent the wheel.

so to get just the initial of any third name can he code

, tblParseTest.Field3 = Left(ParseWord([strFull],3," ",True),1);

Brian

PS I've noticed that Bob has a wealth of useful links

What if the name was something like
First----- Middle----- Last
Billy Bob--Earl------- Mc Allen

I have not tested the fixname sub on those, but by the examples given it looks like it would break.

What if the name was something like
First----- Middle----- Last
Billy Bob--Earl------- Mc Allen

I have not tested the fixname sub on those, but by the examples given it looks like it would break.

Actually, that is why the data should be stored in separate fields to begin with. But, without some very major coding algorhythm, you really aren't going to be able to deal with "exceptions" that well. Those should be handled on a case-by-case basis (I would think). Otherwise, perhaps Billy Bob Earl Mc Allen's middle name is REALLY Bob Earl and his first name just Billy. How do you decide which gets what? And for spaces in the last name, yes you have the Von Trapp family, and all. So, as mentioned, I believe, splitting this out is way harder than putting them back together.

Brian -

Thought you were taking off 'til Monday?! Oh well, seems like you just can't stay away.

Anyway, working with Allen Browne's solution, if you had (and I'm not going to try again to clean-up the formatting -- it took longer than writing the post)

strFull
Doe, John Q.

(from the query)
tblParseTest.Field3 = ParseWord([strFull],3," ",True)

[strFull] represents the string we're attempting to split-up
3 represents the third word we want to pull
" " represents the delimiter between the second and third words (you could also leave it blank since a blank space is the default delimiter)
True represents that leading delimiters (i.e. blank space) are stripped

EraserveAP

Re all of the possible combinations and permutations, e.g. Rev. Billy Bob Earl P. Redneck III

Think Bob Larson described it best. Don't believe it's possible to write all-inclusive code that's going to incorporate every weird combination someone can come-up with.

Anyway, it's best for your health if you avoid folks with similar names.

Best Wishes - Bob

So, as mentioned, I believe, splitting this out is way harder than putting them back together.

No argument there.
My thoughts in my original post with the split function were to avoid such traps in the names. Thus I thought splitting on the comma would alleviate any issues with the Last Name and leave the hard work for where it can not be avoided.

Have you even looked at or downloaded the solution pointed to in Post #5?.

If not, suggest you do it rather than trying to 'reinvent the wheel'. Allen Browne's solution obviously represents hours of work -- and in most cases (weird combinations excluded) it does the job exceptionally well.

Bob

I made a DB for myself which is used to clean up lists of prospects we buy for telemarketing.

What it basically does is

1) The query first splits the entry into two parts and it is also doing replace to remove various characters like / etc.

Billy Bob--Earl------- Mc Allen The first split on that gave

Billy Bob and -Earl------- Mc Allen

2) A series of macro SetValue actions are then run which basically feed the result back to main field and SetVaklue other fields with the result and keep repeating. It then goes to the next record. Basically it lops of the right excep for the first entry and it grabs the first entry and puts that in another field and then sends back what it grabbed from the Right and repeats

It split Billy Bob--Earl------- Mc Allen into each component and dropped the --- It also did the dsame with Billy Bob--Earl--- &&---//- Mc Allen

Of course as Bob larson has just posted you then need to know which is which. You can find the "Mc" and Mac" OK but they won't be always be in the same field.

For example Andrew Mc Donald will land the Mc in a different field to
Andrew John McDonald etc

I made what I have a few years ago when I knew even less than I now know:D so it could be improved but it works for me.

If you only have to do this once or only every now and then my advice would be to cease looking for the magic bullet and just accept that you will need to do a bit of mucking about.

If you want, you are welcome to use what I have. It is easy to use, you just fill the field with your entries and then Click. It handles 6 entities or items. In other words it will split 1 2 3 4 5 6 or 1 &&2 //3 4 & 5 6 so that the 1 2 etc are in different fields but it won't do 7.

But I have no idea how long it would take to run down the record list for 980,000 records:)

Mike -

Post your code.

Bob

Attached. No laughing:D

The form is Split and its query is Split.

If you enter a name directly into the form to test then click another field so the query does the first split.

If you had to heaps of records and did not need the removal of various characters then you simplify the query greatly because Replace is slow.

raskew I am not re-inventing anything; I simply made suggestion in Post#2 which pre-ceded Post #5 and I even said in Post #2 it would probably break if used past splitting on the comma in different wording of course. Given the later suggestions, such as Post #5, I certainly do not suggest that my suggestion in Post #2 be used past the comma if at all.

In Post 13, after reading the “examples given”, I posed a question on where I thought Allen Browne's code may break; I stated then that I had not tested it, this was quite clear. I was concerned, nothing more nothing less.
Bob Larson seemed to confirm this concern in his post because you really can not be sure about names. There is no golden rule to break them up that will work flawlessly for every name. It may parse them flawlessly but who is say exactly where the Last Name begins and the Middle Name ends when you plug them into new fields? That was the whole point of my question in Post #13 and I am sure that was what Bob Larson was trying to relate in Post #14. You can not just rely on where the spaces occur that will fail eventually. Some full names may have more than 3 spaces maybe even as high as 6 who knows for sure? Sure Allen Browne's code will work for the majority of the names in a given list and undoubtedly on some lists it will work for all the names. Rest assured though that you will run into rule breakers at some point, it is a certainty. You can definitely use Allen Browne's code as a base to improve upon, nothing wrong with that.
All I am saying/implying is you or whoever should QC the Table when this code is finished; not doing so could be bad and if you are doing something important or important and work related that would be very bad.

In Post #17 all I did was explain my reasoning for my suggestion in Post #2, and I quote from Post #17 “My thoughts in my original post with the split function were” if that ticked you off I apologize now, it certainly was not my intent. I was simply trying to relate my thought process on it.

By the way Mike375 that is a nice tool that have there.

I have some later versions and also some for cleaning up phone numbers so the format is the same for duplicate checking but I am not presently where my external hard drives are at.

That one I posted I think has a problem with type mismatch on some entries but I can't remember what they were.

I often look back on things I made years ago and marvel at what "work arounds" can achieve:D The one I made to drop all spaces and - _ () etc from phone numbers took me forever to make but now I can do it with a simple function.