Separating Name Query 5

[Sdirby]

I have 2 tables, one holds the full name of an individual all in one field like so:

Field = Borrowername
Data= George W Bush

I need to know if there is a way to parse that between three different tables by way or a query. I'm trying to take

George W Bush

and make it
First Name: George
Last Name: Bush
M I: W

If there is a way to do this please help. There are thousands upon thousands of names to parse and there must be an easier way than rekeying. Thanks for all the help!

Sean

- a clean desk is the work of an idle mind

 

[Golom]

There are ways using SQL but, since this is a one-time (I assume) update, I would get a recordset and process it record by record. The reason being that records like
[tt]
Madonna Just one Name
John Doe First and Last Only
Tom T. Terrific First, Middle and Last
Dr. William Wood Title, First and Last
Mr. Ted G. Smith All of them
[/tt]
Require distinctly different processing which is best handled in code rather than some "one-size-fits-all" SQL.

 

[lespaul]

The following should work:

This will split the name in two (based on the first ' ' in the BorrowerName):

FirstName:Left([BorrowerName], Instr([BorrowerName]," ")-1)


LastName:Mid([BorrowerName], Instr([BorrowerName]," ")+2)

then you would need to do the following to extract the middle intital:

MiddleInit:Left([LastName], Instr([LastName]," ")-1)

LastName:Mid([LastName], Instr([LastName]," ")+2)

HTH

Leslie

 

[scriverb]

I just wrote a query for friend that does just as you have requested. The following should do the trick:

SELECT YourTableName.[BorrowerName], Count_Chars([YourTableName]![BorrowerName]," ") AS CountOfSpaces, Switch(Count_Chars([YourTableName]![BorrowerName]," ")=1,Mid$([BorrowerName],InStr(1,[YourTableName]![BorrowerName]," ")+1),Count_Chars([YourTableName]![BorrowerName]," ")>1,Mid$([BorrowerName],InStr(1,[YourTableName]![BorrowerName]," ")+3)) AS TenantLast, Mid$([BorrowerName],1,InStr([BorrowerName]," ")) AS TenantFirst, IIf(Count_Chars([YourTableName]![BorrowerName]," ")>=2,Mid$([BorrowerName],InStr(1,[YourTableName]![BorrowerName]," ",1)+1,1)," ") AS TenantMiddle
FROM YourTableName
ORDER BY Count_Chars([YourTableName]![BorrowerName]," "), Switch(Count_Chars([YourTableName]![BorrowerName]," ")=1,Mid$([BorrowerName],InStr(1,[YourTableName]![BorrowerName]," ")+1),Count_Chars([YourTableName]![BorrowerName]," ")>1,Mid$([BorrowerName],InStr(1,[YourTableName]![BorrowerName]," ")+3)), Mid$([BorrowerName],1,InStr([BorrowerName]," ")), IIf(Count_Chars([YourTableName]![BorrowerName]," ")>=2,Mid$([BorrowerName],InStr(1,[YourTableName]![BorrowerName]," ",1)+1,1)," ");

There is an additional column generated which is a count of the number of single spaces found in the string. This tells me if there truly is a Middle initial or not. If just 1 space then we have just a first and last name. If greater than 1 we have first name, middle initial, last name and maybe a suffix(i.e. Jr. III, IV etc.) This code should give you close to what you are looking for.

Post back with any questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Sdirby]

I wish it were only a one time update. There will be one major update that uses it, but after that has been done it will be on a daily basis due to a partner company not wanting to compromise. Any help is greatly appreciated. I will list some info i think might be helpful. The names are all either 2 or 3 words. First MiddleInitial Last. That is the format used, only some are 2 words. First Last. Knowing this, is there a way to parse these out into the 3 separate fields.

 

[Sdirby]

Thank you all i'll try it and let you know!

 

[Golom]

OK. scriverb's SQL should handle it ... provided that the formatting is as uniform as you believe it to be. Note however that users are notoriously unreliable. You can get things like
[tt]
John A.Doe Missing a space
Robert E Smith More than one space
Jones, Mary S. First and last reversed (indicated by the comma)
[/tt]
To the extent that such things happen, you will get inconsistent results from approaches that rely on finding spaces and parsing based on them.

 

[lupins46]

Well the solution will only be as good as the data is consistent but I guess any help is worth it.
This will handle names like geo w bush and geo bush bit not geo bush jnr


Create an update query and update the fields as follows:

First Name --
Left(borrowername, InStr(1, borrowername, " ") - 1)

middle :
Mid(borrowername, IIf(InStr(InStr(1, borrowername, " ") + 1, borrowername, " ") = 0, InStr(1, borrowername, " "), InStr(InStr(1, borrowername, " ") + 1, borrowername, " ") + 1), 1)


lastname :
StrReverse(Left(StrReverse(borrowername), InStr(1, StrReverse(borrowername), " ") - 1))
Debug.Print lastname

 

[scriverb]

Thanks for the word of confidence Golon. But, I forgot that I had written a couple of functions for this to work. Please copy and paste the following into a database module

Public Function InStrRight(vSearchStr As String, vTargetStr As String, vStart As Long) As Long
Dim i As Integer
Stop
  For i = vStart To 1 Step -1
      If InStr(i, vSearchStr, vTargetStr, 1) = i Then
          InStrRight = i   'Position of vTargetStr
          Exit For
      End If
  Next i
End Function

Public Function Count_Chars(vSearchStr As String, vTargetStr As String) As Integer
Dim i As Integer
Dim vCount As Integer
vCount = 0
  For i = 1 To (Len(vSearchStr) - 1)
      If Mid$(vSearchStr, i, 1) = vTargetStr Then
          vCount = vCount + 1
      End If
  Next i
Count_Chars = vCount
End Function

Yes, I know that the data may be a little distorted but this will take care of most of the names if the format that was posted is half-way reliable. The others can be modified as needed.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???