Parsing a Name into first, last and Mi 2

[Trogg]

I have a table with a concatenated name field that I need to separate into First, Last, and MI in a query. The problem is one of inconsistent data entry. Here are some samples:

Name
BELL,FRED J
BLACK,MATTHEW
PLUMBERG, ANDY
BEALS IV,JOHN C

Here are the strings I am using:
Last Name: Left([Name],InStr([Name],",")-1)
First Name: Mid([Name],InStr([Name],",")+1,InStr([Name] & " "," ")-(InStr(([Name]),",")+1))

Middle Initial: Mid([Name],InStr([Name] & " "," ")+1,1)

These statements work perfectly on names like the first 2 examples, however if a name happens to have a space after the comma it fails to get the first name and puts the first letter of the first name as the middle initial. Also if the name has a "III" or "IV" as in the last example I get a "#Error" for first name and the "I" in "IV" as a middle initial. Can you help me take care of these possibilites in my extractions? Thanks in advance!

 

[GingerR]

Strip the lastname and comma off and use TRIM before doing the first name.

To get first name, take:

Right([Name],Len([Name])-(Len(LastName)+1))

So you'd be starting with
FRED J
MATTHEW
ANDY
JOHN C

TRIM that (get rid of spaces before and after)
And take the stuff left of any 'space'
I'm sure you can figure out Middle Initial from there.

Lots of embedded functions, but fun



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Trogg]

Hi Ginger!

Very creative use of the Last name field to get the correct first name! I did not think of trying that. Can you tell me how to strip those trailing initials from the first name now? I've tried to do it a few ways but can't seem to hit the right combo... sorry I'm not getting it and sorry to bother you again. Thanks for your help!

 

[GingerR]

So far you should have a string that is FIRST NAME or FIRST NAME and Space and MI.

If this string isn't trimmed, Trim it.

Use Instr to detect a space or not

If no space, you're done there's no initial

If there's a space, get the one character to the right of the space, that's the MI. Then set First Name to the Left number of characters that is the (LEN of this string - 2) (the space and initial).

get it? Just keep playing with Trim, Left, Right, Instr.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[AlexCuse]

Once you figure out how to parse these consistently, I would suggest putting the functions into a code module so you don't have to do it in every query that you need this functionality in.

Hope this helps,

Alex

[small]----signature below----[/small]
Numbers is hardly real and they never have feelings but you push too hard, even numbers got limits
Why did one straw break the camel's back? Here's the secret: the million other straws underneath it

My Crummy Web Page

 

[traingamer]

And think carfeully about what to do with exceptions like

Van der Wooten IV, Linus D

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[GingerR]

It wouldn't be an exception:

Everything to the left of the comma is the last name: Van derWooten IV

Trim what's remaining: Linus D
It has a space, so the character to the right of the space is the MI.
Take that off, trim it, and what's left is the first name.

Same as above.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[traingamer]

My mistake. I completely misread the original question - I thought (s)he wanted to trim the 'IV' from the last name.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Trogg]

Thank you all for the help so far, I have been at this for 4 days now but still have not hit on the proper combinations and am about to go over the edge.. please help if you can figure out where I'm missing it. Ginger I am still trying to remove the MI from your First name solution but cannot seem to get the string parsed correctly. Can anyone help with the solution please?

Getting the middle initial has also been a challenge... here is what I have so far. Using Gingers first name string to try to get the MI...

Trim(Mid([First Name],InStr([First Name]," "),IIf(InStr(InStr([First Name]," "),[First Name]," ")=0,0,InStr(InStr([First Name]," "),[First Name]," ",1))))

This works fine on names with middle initials but returns "#ERROR" on names without middle initials. I assume it is because there is no space to check for at the end of the string when there is no middle initial? I know this shouldn't be this difficult and it is really making me feel stupid. Thanks for your help.

 

[GingerR]

Here's the query code: see the field Remainder1, it's the right-part of the Name field to the right of the comma; the other fields to the right of it use Remainder1 for their calculations. I left Remainder1 in there so you can see how the pieces are put together. I just did this by continually fiddling with it til it came together, one piece at a time.


You have to substitute your table name in the second word (tblNames.) and last word for [tblNames]. So I did this:

1) LastName (used your code)
2) Remainder1 = TRIM(Stuff to the right of the comma)
3) MI = If there's no space in Remainder1, MI = null; if there IS a space in Remainder1, MI = the right-most single character.
4) FirstName: If there's no space in Remainder1, FirstName = Remainder1; If there is a space in Remainder1, Firstname = what's to the left of the space.

SELECT tblNames.Name, Left([Name],InStr([Name],",")-1) AS [Last Name], Trim(Right([name],Len([Name])-InStr([Name],","))) AS Remainder1, IIf(InStr([Remainder1]," ")=0,Null,Right([Remainder1],1)) AS MI, IIf(InStr([Remainder1]," ")=0,[Remainder1],Trim(Left([Remainder1],InStr([Remainder1]," ")))) AS FirstName
FROM [tblNames];

p.s. You probably shouldn't use "Name" as a field name since it's an Access keyword and will most likely honk you up a million times later on....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[HarleyQuinn]

Further to Alex's suggestion about a code module, if you do that you'll be able to use Split() on the field making it alot easier to achieve your desired result.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Trogg]

THANKS GINGER! It looks like i ws trying to overthink and complicate the parse. What you have will work fine and very little editing will have to be done due to inconsistent data entry. Stars to you!.. and thanks for keeping me from going insane! :>)

 

[HarleyQuinn]

Just because I'm a bit bored at the moment (and just for the hell of it, maybe someone might want it one day) I've knocked up the UDF using Split() I mentioned earlier. Pass in the field and then either F,M or L for whichever part of the name you're after.

Function SplitNames(strName As String, strPart As String) As String

Dim arrSplit() As String

arrSplit = Split(strName, ",", -1)

Select Case UCase(strPart)

   Case "L"
      SplitNames = Trim(arrSplit(0))
   Case "F"
      SplitNames = Split(Trim(arrSplit(1)))(0)
   Case "M"
      SplitNames = vbNullString
      If InStr(Trim(arrSplit(1)), " ") Then SplitNames = Split(Trim(arrSplit(1)))(1)
      
End Select

End Function

Seems I've got something to actually do now (woot!) it will still throw an error instead of a value if the field it's querying is blank (like GingerR's) but it might give you something to do

Hope this is helpful to someone.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

OK, that was a brain fart moment!

Inside the function call wrap the field name in

NZ(<fieldname>,"")

and put back in the error handling I originally took out (stick it after you dimension the array):

If Trim(strName & "") = "" Then
   SplitNames = vbNullString
   Exit Function
End If

Sorted.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Trogg]

Wow Harley! You were extremely bored! thanks... :-D

 

[HarleyQuinn]

Yes I was

You're welcome

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.