Make multiple fields from one field.

[smittick]

I have a table that has a filed called FullName. I want to separate FullName
into 3 fields LastName, FirstName, MiddleI. The last name, first name and
middle initials are separted by one space.

 

[Tom Wickerath]

Hi Smittick,

One of my favorite utilities for splitting names in Access is called
"Splitter for Microsoft Access". If you don't mind popping $39 for a copy,
you will have a very good utility that can untangle the messiest name data:

http://www.infoplan.com.au/splitter/

A very easy method to use involves exporting the table to Excel. From within
Excel, use Data > Text to Columns... Then re-import your table back into
Access.

You can also create the new fields in the table and then use an update query
to populate the new fields. The update query would look like this:

Field: LastName
Table: Your tablename
Update To: SplitName([FullName],0)

Field: FirstName
Table: Your tablename
Update To: SplitName([FullName],1)

Field: MiddleInitial
Table: Your tablename
Update To: SplitName([FullName],2)

Create a new standard module. Then copy and paste the following function
into it. This will create a zero-based array named strResult. The On Error
Resume Next statement will prevent the function from choking to a halt if
there is no middle name for a given record.

Public Function SplitName(FullName As String, intElement As Integer) As String
On Error Resume Next

Dim strResult() As String
strResult = Split([FullName], Chr(32))

SplitName = strResult(intElement)

End Function




Tom
____________________________________

:

I have a table that has a filed called FullName. I want to separate FullName
into 3 fields LastName, FirstName, MiddleI. The last name, first name and
middle initials are separted by one space.

 

[smittick]

Thanks I have been wondering around in the dark. Access help was no hekp.

 

[Arvin Meyer [MVP]]

I have a table that has a filed called FullName. I want to separate FullName
into 3 fields LastName, FirstName, MiddleI. The last name, first name and
middle initials are separted by one space.

Here's some code that Terry Wickenden posted some time ago which should do
most of what you want. With a little experimentation you should be able make
it work for your requirement:

Below are 3 functions I developed a few years ago. They extract Title,
First Name(s) and Surname. Hopefully with a bit of thought you can adapt
them to also pull out the Suffix parts of names

Function NameTitle(strPass As String) As String
' Accepts: A string variable
' Purpose: Find title name within
' Returns: A truncated string containing title or empty string
' Created: 18/10/96 Terry Wickenden
' Note: Assumes if there is a title that it is one of the following
' Dr, Mr, Mrs, Ms, Miss

Dim intPos1 As Integer
Dim strTemp As String

intPos1 = InStr(1, strPass, Chr(32)) 'Find first space
If intPos1 = 0 Then 'Only one word
NameTitle = "" 'Return zero length string
Exit Function
End If

strTemp = Left(strPass, intPos1 - 1)
Select Case strTemp
Case "Dr", "Mr", "Ms", "Mrs", "Miss", "Dr.", "Mr.", "Ms.", "Mrs."
NameTitle = strTemp
Case Else
NameTitle = ""
End Select

End Function

Function NameFirst(strPass As String) As String
' Accepts: A string variable
' Purpose: Find Christian name within
' Returns: A truncated string containing first name
' Created: 18/10/96 Terry Wickenden
' Note: Assumes if there is a title that it is one of the following
' Dr, Mr, Mrs, Ms, Miss

Dim intPos1 As Integer
Dim intPos2 As Integer
Dim intLen As Integer
Dim strTemp As String

strPass = Trim(strPass)

intPos1 = InStr(1, strPass, Chr(32)) 'Find first space
If intPos1 = 0 Then 'Only one word
NameFirst = strPass
Exit Function
End If

intPos2 = InStr(intPos1 + 1, strPass, Chr(32)) 'Find next space
If intPos2 = 0 Then 'Only two words
NameFirst = Left(strPass, intPos1 - 1)
Exit Function
End If

'if 3 words check to see if first word is title
strTemp = Left(strPass, intPos1 - 1)
Select Case strTemp
Case "Dr", "Mr", "Ms", "Mrs", "Miss", "Dr.", "Mr.", "Ms.", "Mrs."
intLen = intPos2 - intPos1 - 1 'Calculate length of 2nd
word
NameFirst = Mid(strPass, intPos1 + 1, intLen)
Case Else
NameFirst = strTemp
End Select

End Function

Function NameLast(strPass As String) As String
' Accepts: A string variable
' Purpose: Find surname within
' Returns: A truncated string containing all of string from
' 2nd space onwards or an empty string if only one word is passed
' Created: 18/10/96 Terry Wickenden
' Note: Assumes if there is a title that it is one of the following
' Dr, Mr, Mrs, Ms, Miss

Dim intPos1 As Integer
Dim intPos2 As Integer
Dim intLen As Integer
Dim strTemp As String

strPass = Trim(strPass)

intPos1 = InStr(1, strPass, Chr(32)) 'Find first space
If intPos1 = 0 Then 'Only one word
NameLast = "" 'Return zero length string
Exit Function
End If

intPos2 = InStr(intPos1 + 1, strPass, Chr(32)) 'Find next space
If intPos2 = 0 Then 'Only two words
NameLast = Mid(strPass, intPos1 + 1)
Exit Function
End If

'if 3 words check to see if first word is title
strTemp = Left(strPass, intPos1 - 1)
Select Case strTemp
Case "Dr", "Mr", "Ms", "Mrs", "Miss", "Dr.", "Mr.", "Ms.", "Mrs."
NameLast = Mid(strPass, intPos2 + 1) 'Return third word
Case Else
NameLast = Mid(strPass, intPos1 + 1) 'Return rest of string
End Select

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access