Changing format of Name fields

[DirtyB]

I have in a field here your normal "LastName, FirstName". I need to make a query that takes the firstname and lastname and separate them in new fields called 'FirstName' and 'LastName'. Does anyone have handy a query that will separate a string like this?

Thanks

 

[THoey]

Depending on your version, A2K has a split function that can help you do this. If not on A2K several people have written functions like this on Tek-Tips Access forums. Try doing a search or look in the FAQ. I am sure it is there. If you can't find it, reply back and I'll see what I can find... "Alcohol is our friend, and it's about time we had more friends over."

Terry M. Hoey

 

[DirtyB]

I apologize for not looking in the FAQ's first, I usually try to do that. I did find a good faq on doing this. The faq had the following code in it that takes one field and separates it into three new fields created by the user prior to running this code:


Function ParseName()
'This function breaks apart a single name field into seperate first, last & middle initial fields.
'This will work for a name entered in any of the following ways:
' {Smith, John} {Smith, John D} {Smith, John D.}
' {Smith,John} {Smith,John D} {Smith,John D.}
' {Smith John} {Smith John D} {Smith John D.}

'Start by adding 3 fields to the table where your single name field is (firstname, lastname, & MI)

On Error GoTo Parse_Err

Dim db As Database
Dim rs As Recordset
Dim fldName As Field
Dim x As Integer
Dim strLast As String, strFirst As String, strMI As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableNameHere", dbOpenDynaset) 'open appropriate table
Set fldName = rs![NameFieldHere] 'single name field

DoCmd.Hourglass True
Do Until rs.EOF
If IsNull(rs!FirstName) Then
x = InStr(1, fldName, ",&quot
If x = 0 Then
x = InStr(1, fldName, " &quot
strLast = Left(fldName, x - 1)
strFirst = Mid(fldName, x + 1)
If Right(strFirst, 1) = "." Then
strMI = Right(strFirst, 2)
strFirst = Left(strFirst, Len(strFirst) - 3)
Else
If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then
strMI = Right(strFirst, 1)
strFirst = Left(strFirst, Len(strFirst) - 2)
Else
strMI = ""
strFirst = strFirst
End If
End If
Else
If Mid(fldName, x + 1, 1) = Chr(32) Then
strLast = Left(fldName, x - 1)
strFirst = Mid(fldName, x + 2)
If Right(strFirst, 1) = "." Then
strMI = Right(strFirst, 2)
strFirst = Left(strFirst, Len(strFirst) - 3)
Else
If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then
strMI = Right(strFirst, 1)
strFirst = Left(strFirst, Len(strFirst) - 2)
Else
strMI = ""
strFirst = strFirst
End If
End If
Else
strLast = Left(fldName, x - 1)
strFirst = Mid(fldName, x + 1)
If Right(strFirst, 1) = "." Then
strMI = Right(strFirst, 2)
strFirst = Left(strFirst, Len(strFirst) - 3)
Else
If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then
strMI = Right(strFirst, 1)
strFirst = Left(strFirst, Len(strFirst) - 2)
Else
strMI = ""
strFirst = strFirst
End If
End If
End If
End If

With rs
.Edit
!LastName = strLast
!FirstName = strFirst
!MI = strMI
.update
.MoveNext
End With
Else
rs.MoveNext
End If
Loop
DoCmd.Hourglass False

rs.CLOSE
db.CLOSE

Parse_Exit:
Exit Sub

Parse_Err:
DoCmd.Hourglass False
MsgBox Err.Number & ": " & ErrDescription
Resume Parse_Exit
End Function


I keep getting a type mismatch error. The field that I am selecting from has the same format and datatype as the three fields that I am trying to populate. Anyone have any ideas?

 

[MichaelRed]

It would appear that the function REQUIRES three elements and you only provide two. It fails on the lack of the third.

For suggestions:

1)[tab]Follow terry's suggestion re "Split" (or basSplit for ver '97)

2)[tab]Modify the above to work with either a variable number of "words" or two work with two "words".

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over