"exploding" a field in a record

[Hanksor]

I need to take a field that has a full name "First M Last" and split it
into three different fields. All names use a space to seperate the first,
last, and middle initial. Access 2002. Any help will be appreciated.

 

[MGFoster]

I need to take a field that has a full name "First M Last" and split it
into three different fields. All names use a space to seperate the first,
last, and middle initial. Access 2002. Any help will be appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


You can play w/ Left$(), Right$(), Mid$() and Instr(), but the easiest
way is to use Split(name_column, " "). Unfortunately you can't do that
in a query... But, you can call a user-defined function that uses the
Split() function. E.g.:

Public Function GetName(strFullName As String, intIndex As Integer)

Dim s() As String

s() = Split(strFullName, " ")

GetName = s(intIndex)

End Function

Call it from a query like this:

SELECT GetName(NameColumn, 1) As FirstName, GetName(NameColumn,2) As
MiddleName, GetName(NameColumn,3) As LastName
FROM <table name>
WHERE ... etc. ...

The Split function splits the indicated string into as many pieces as
are separated by the "separator" token (the space in this case). Since
your column has 3 "things" separated by a space you should get a 3
element array. By sending the full name & the array index that you want
returned (1=first name, 2=middle name, 3=last name) the function splits
the name string and returns the indicated index.

You'll have to do some checking for "no middle name" strings and other
possible errors.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRSMN9oechKqOuFEgEQLXIwCg3DLCWyJnothz4YXdouBgqNtN/AEAoOFg
YvDuVuwe0DlJDV3E49iMhO2K
=NWye
-----END PGP SIGNATURE-----

 

[Marshall Barton]

I need to take a field that has a full name "First M Last" and split it
into three different fields. All names use a space to seperate the first,
last, and middle initial. Access 2002. Any help will be appreciated.

If you can actually enforce that format on your data entry
folks (which I doubt), use something like

Fname = Left(fullname, InSt(fullname, " ") - 1
Mi = Mid(fullname, InSt(fullname, " ") + 1, 1)
Lname = Mid(fullname, InSt(InSt(fullname, " ") + 1,
fullname, " ") + 1

But what are you going to do for people with names like
J Thomas Keller
Cathryn Mary Zeta Jones
Claus von Ricthoven
Zena

where it's not clear what parts are the middle and last
names or even if they exist???


This is why the rules of database normalization insist on a
single value in a field. If you are going to use separate
parts of a name, they need to be stored in different fields
in the table.