Splitting Concatenated Fields - HELP!! 2

[dzdncnfsd]

I need to split a name field into two fields (FirstName, LastName) in a query, but not change the underlying table. The original field contains many combinations of first, middle, and last such as:
Mary Jane Doe
John D. Doe
Mary Jane Von Trapp
John D. Doe Sr.
John Doe
I think you can see what the problem is. If this cannot be done in a query, I guess the next best alternative would be to create a new table that contains the separated fields. And it needs to be done on a daily basis in order to pick up any new records. I have searched this forum for clues, but most answers assume there is a delimiter that can be used as a reference point. I appreciate any help you can give me.
Thank You.
Gladys X-)

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[ghubbell]

Hi Gladys,
Because your data is quite scattered I'll give you a query that you can work with to see how you can solve this without getting too fancy. The first thing I'll need you to do is create a table called "tblcustomer" . Add 1 field to it (text) and name the field "Customer". Close and save. You don't need any indexes or other MS recommended stuff.
Open it in datasheet view and for fun, type in a few of your names.

Now open a new query in design view. Switch to SQL view which is one of the choices on the design button (ruler, triangle and pencil). Paste this in:

SELECT tblcustomer.Customer, InStr([Customer]," &quot AS TheSpace, Left([Customer],[Thespace]-1) AS TheResults, InStr([thespace]+1,[Customer]," &quot AS NextSpace, Mid([customer],[thespace]+1,[nextspace]-[thespace]-1) AS 2ndResults, Mid([customer],[thespace]+1) AS BalAfterTheResults
FROM tblcustomer;

After the paste, switch to datasheet view and see what you have. You can use this principle on a copy of your good table and by creating a few new fields "FirstName", "MiddleName" etc. and adding these fields to the same type of query, you can copy entire rows and paste in to your new fields in one shot. You will have to watch for errors in results so I would propose that you don't just extract the first name or "first word", you remove it and its proceeding space. Again just toss in a few extra fields and you'll be able to keep changing your initial "string" to a shortened string. Then running it again. You may choose to set the criteria under the first test to Not Is Null in order to weed out any blanks. Write back if you have any hassles...!


Gord
ghubbell@total.net

 

[link9]

Looking at your examples above, it appears that there is, in fact, a delimeter (the space) --

How would you do this task without some sort of delimeter??

There is a vb (and VBA, too) function called split that will take those above strings and put them into different elements of an array...

example:

mystring = "How now brown cow?"
dim myArray()
myarray = Split(mystring)

You now have a four element array with
myarray(0) = How
myarray(1) = now
myarray(2) = brown
myarray(3) = cow?

You can then do whatever you need to do with those four elements...

If you need to get rid of any punctuation, then use the replace function...

replace(myString, "?", "")

Just put whatever you want to can in the first quotes and whatever you want there, instead, in the second quotes. Then, if you run the same split routine on myString, you will be free of that trailing '?' on the end of 'cow'

hope it helps!
Paul Prewett

 

[dzdncnfsd]

Thanks so much for your responses.
Gord, I used your instructions (thank you for making it simple) and it works great on FirstName, still a few problems with LastName. For instance:
FullName TheResults 2ndResults BalAfterResults
Mary Jane Doe Mary Jane Jane Doe
However, you did give me a great base to work from and I will continue tweaking it. Thanks.

Paul,
The split function you describe sounds great. Is it available in Access 97, and if so, can it be used in the SQL view of a query or only in a module for a report or form?
Thanks,
Gladys




Gladys Clemmer
gladys.clemmer@fifsg.com

 

[ghubbell]

Way to go Gladys! Exactly my hopes and I'm sure with that "tweeking" you'll dial it in exactly as you need. I did make one boo boo (technical term) in my explanation: and that is you can copy entire columns not rows...but you probably figured that out anyway. Thank you, Gord
ghubbell@total.net

 

[dzdncnfsd]

I love it when I get a push in the right direction but still have a few things left to iron out. I was able to parse the names out exactly as I wanted by doing this:
Select
InStr([FIC-NAM1]," &quot AS FirstSpace,
InStr([FirstSpace]+1,[FIC-NAM1]," &quot AS NextSpace,
Left(Left([FIC-NAM1],[FirstSpace]-1) & Space(16),16) AS FirstName, Left(IIf([NextSpace]=0,Trim(Mid([FIC-NAM1],Val([FirstSpace]),30)),Trim(Mid([FIC-NAM1],Val([NextSpace]),30))) & Space(30),30) AS LastName FROM dbo_ShawMiniMaster_Current
Not only did I need the names separated, but they had to be output as fixed length fields.
Thanks again!!
Gladys


Gladys Clemmer
gladys.clemmer@fifsg.com

 

[link9]

I'm not sure if that's available in '97 -- but if it is, it's only available in modules, as it's vb code, not SQL --

Glad you got it worked out, anyway ---

 

[Quehay]

I'm going to be a spoiler here: you can export the table to Excel and use Data > Text to Columns and split based on your choice of delimiter (space, comma, tab, etc.). This allows scanning the entire column to see the results. Be sure to insert several blank columns to the right of the column to be parsed.

 

[ghubbell]

A Swell idea! where were you an hour ago?!! Gord
ghubbell@total.net

 

[dzdncnfsd]

Thanks, Quehay. I will remember that for future projects because I have to do this quite often. Fortunately, the source fields aren't usually as screwed up as the name field I was working with today.
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[MichaelRed]

Search for "basSplit" in the Ms. Access Forums. I generated the function for some "Ms. Accesser's" a while back. It is strictly a procedure, so direct use in a query doesn't work, however I have used it to do similar things by creating a Table with the foregin key from the source table, the "Full Name" and NameWord1 ... NameWordN[/i} fields to hold the results. First an Update query from the souorce table to add any new names and foregin Keys (based on Primary Key). Then, just loop through the table. If NameWord1 is Blank, call basSplit to get the words in the FullName broken down into the individual words. Do an update to the record using each element in the array of words returned from basSplit.


MichaelRed
redmsp@erols.com

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