Splitting Contents of a Text Field 1

[awl]

I have a field that lists the last name, first name, middle or middle initial with a period all in one cell, Examples Given:

Lager, Curtis A.
Lager, Curtis Adrian
Lager, Sr, Curtis A.
Lager Curtis Adrian, Sr.

The staff is not consistent except for the last name. I want to create a query that does the opposite of “concatenation”, breaking down (splitting) this text field by using the comma, period and space as dividers. I know of the Right and Left and Trim, however each name has a different number of characters. Can this be accomplished in Access and if so, can you provide a sample? Thanks.

 

[PHV]

A starting point in VBA:
x = "Lager, Sr, Curtis A."
a = Split(Trim(Replace(Replace(x, ".", " "), ", ", " ")))
For i = 0 To UBound(a)
x = x & vbCrLf & "a(" & i & ")=" & a(i)
Next i
MsgBox x

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[awl]

PH, thank you very much for your response. I can see that you are well above my knowledge of VBA and MS Access. I must say though I don’t use VBA, I’m learning though. However, I would like to ask where would I place this coding in my database? The field [Name] in the tblMFS Master Data List as addressed above has a list of 2051 different individuals and if I may to help understand, why was the "Lager, Sr, Curtis A." selected as being equal to x? And if possible, in my query I want one field to be [LastName] only, second field [FirstName] and third [MiddleName]; so what kind of SQL would be appropriate to take just the last name out of the [Name] field from the table as it relates to the VBA coding, as each last name has a different amount of characters? Thanks for your help.

 

[lespaul]

you would need to put this code in a module. You would want to set up the function to take an input (which is what PHV is simulating when he sets x = that name). There are several threads that have very detailed instructions on what you are trying to do. I'll search and if I can find some I'll post a link.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Eupher]

awl,
The number of characters in the name (or any part of the name) is not relevant. PHV's example is, as noted, a starting point only. I'll try to break it down for you to help you understand what his code does and how that might apply to your task. I apologize in advance if I'm spewing info you already know.

First, PHV hard codes one of the names in your list (as an example of the technique, only) and assigns it to a string variable "x". The next line nests several functions. Working from the inside out, the first Replace function searches for all periods in the name string and replaces them with a space. Then all commas are replaced with a space. Then the Trim function removes any leading or trailing spaces from the entire string. The Split function does exactly that: it splits the string into pieces using the default delimiter, the space character (which is why PHV used it in the Replace functions) - and assigns the parts to the elements of an array variable "a". So a(0) = "Lager", a(1) = "Sr", a(2) = "Curtis", and a(3) = "A". PHV then loops through the elements of the array and builds another string to demonstrate in a message box how the original string was broken up and its parts assigned to the array.

Your task is made more complex by the inconsistency of the data, namely the placement of the suffix ("Sr."). Not quite sure how would be the best way to handle that - perhaps as you loop through the elements of the array assigning the parts of the name to fields in your table, test for the most common suffixes (Sr., Jr., II, III) and if found, place them in their appropriate field.

HTH,

Ken S.