Access rookie...need help separating a field...

[ncstateguy]

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!

 

[EraserveAP]

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.

 

[MSAccessRookie]

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.

 

[ncstateguy]

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.

 

[raskew]

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

 

[MSAccessRookie]

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.

 

[ncstateguy]

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?

 

[MSAccessRookie]

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.

 

[Brianwarnock]

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

 

[raskew]

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

 

[raskew]

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

 

[boblarson]

Suggest you give it a try.

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

 

[Brianwarnock]

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

 

[EraserveAP]

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.

 

[boblarson]

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.

 

[raskew]

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

 

[EraserveAP]

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.

 

[raskew]

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

 

[Mike375]

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 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

 

[raskew]

Mike -

Post your code.

Bob