If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"exploding" a field in a record
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. -- Thanx, Hanksor Oregon City, Or |
#2
|
|||
|
|||
"exploding" a field in a record
Hanksor wrote:
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/AwUBRSMN9oechKqOuFEgEQLXIwCg3DLCWyJnothz4YXdouBgqN tN/AEAoOFg YvDuVuwe0DlJDV3E49iMhO2K =NWye -----END PGP SIGNATURE----- |
#3
|
|||
|
|||
"exploding" a field in a record
Hanksor wrote:
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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|