|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Divide One Field into 3 partsI have a Name field that I would like to divide into 3 seperate parts?
example: John W Smith Lisa Marie Jones I would like the the result to be 3 different fileds: First, Middle, Last Name Is this even possible? On Wed, 1 Jul 2009 15:48:02 -0700, KrispyData
<KrispyD***@discussions.microsoft.com> wrote: >I have a Name field that I would like to divide into 3 seperate parts? Possible but tedious. What will you do with John Ronald Reuel Tolkien? Or> >example: >John W Smith >Lisa Marie Jones > >I would like the the result to be 3 different fileds: First, Middle, Last >Name > >Is this even possible? Madonna? Or Rhoda Mae Johnson (whose first name is Rhoda Mae, just ask her)? With someone who has no middle name? I'd suggest adding FirstName, MiddleName and LastName fields to the table, and then run a series of update queries. First update FirstName to Left([Name], InStr([Name], " ") - 1) using a criterion of LIKE "* *" on name to be sure there IS a blank in it. In the same query update LastName to Mid([Name], InStr([Name], " ") + 1) Then in a second query, use a criterion of LIKE "* *" on LastName and update MiddleName to Left([LastName], InStr([LastName], " ") - 1) and LastName to Mid([LastName], InStr([LastName], " ") + 1) Then do a search of all three fields using a criterion of LIKE "* *" OR IS NULL to find any remaining odd blanks. You should find NULL values only for MiddleName (for people with no middle name) but there might -- John W. Vinson [MVP] John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:kitn45ddahea4br38nrapah3ard6hs9tof@4ax.com: I long ago abandoned middle name/middle initial fields. That's not> I'd suggest adding FirstName, MiddleName and LastName fields to > the table data that you're ever going to search on by itself, so you save yourself all sorts of concatenation issues, though with Trevor Best's Mid() Null propagation trick, it's not that hard: Mid(("12"+LastName) & (", "+FirstName) & (" "+MiddleName), 3) (there is a problem with that, because for it to work reliably, you need the same number of characters null-propagated with each field. But you'll never have a middlename without a firstname, so this works). (I also use it for addresses: Mid(("12"+Address1) & (CrLf()+Address2) & (CrLf()+Address3), 3) [CrLf() is a function that returns vbCrLf] Given that your newline is two characters, it works out pretty well.) Hi John:
I'm a little confused. Are you suggesting I create an update query and use the expression you indicated? Also, I tried going on dfenton's website and it was a violinist site?? On Mon, 6 Jul 2009 09:00:02 -0700, KrispyData
<KrispyD***@discussions.microsoft.com> wrote: >Hi John: If you're trying to update LastName, FirstName etc. fields in your table, then> >I'm a little confused. Are you suggesting I create an update query and use >the expression you indicated? >Also, I tried going on dfenton's website and it was a violinist site?? yes, you will need update queries. Reread my message: I suggested two update queries run in succession. No idea about the website. -- John W. Vinson [MVP] David has multiple interests and abilities. The first link has a link at
the bottom along the lines of "How I earn a living", which goes to the consulting web site, which has various examples and tips, as I recall. The second link goes directly to the consulting web site. Show quoteHide quote "KrispyData" <KrispyD***@discussions.microsoft.com> wrote in message news:41C0C49B-2526-4AC3-9526-A6DD28010106@microsoft.com... > Hi John: > > I'm a little confused. Are you suggesting I create an update query and > use > the expression you indicated? > Also, I tried going on dfenton's website and it was a violinist site?? Possible, yes.
Easy, maybe... Are you very VERY certain you don't have any names like: Cher Jean Claude van Damm Sir Edmond Hillary As you can imagine, trying to run one of those names through your '3 parts' routine would lead to incorrect results. Regards Jeff Boyce Microsoft Office/Access MVP Show quoteHide quote "KrispyData" <KrispyD***@discussions.microsoft.com> wrote in message news:993CB824-1FF9-4A4C-BDFE-0C89207DAD24@microsoft.com... >I have a Name field that I would like to divide into 3 seperate parts? > > example: > John W Smith > Lisa Marie Jones > > I would like the the result to be 3 different fileds: First, Middle, Last > Name > > Is this even possible? > It's not foolproof because of the structure of certain names but in your
example you can use the Instr function to look for spaces to separate the name into parts. Look at the Instr function in the Help file. Steve san***@penn.com Show quoteHide quote "KrispyData" <KrispyD***@discussions.microsoft.com> wrote in message news:993CB824-1FF9-4A4C-BDFE-0C89207DAD24@microsoft.com... > > I have a Name field that I would like to divide into 3 seperate parts? > > example: > John W Smith > Lisa Marie Jones > > I would like the the result to be 3 different fileds: First, Middle, Last > Name > > Is this even possible? > Steve wrote:
Show quoteHide quote > It's not foolproof because of the structure of certain names but in your Anyone else noticed that our old "friend" Steve is advertising in every > example you can use the Instr function to look for spaces to separate the > name into parts. Look at the Instr function in the Help file. > > Steve > san***@penn.com > > > > > "KrispyData" <KrispyD***@discussions.microsoft.com> wrote in message > news:993CB824-1FF9-4A4C-BDFE-0C89207DAD24@microsoft.com... >> I have a Name field that I would like to divide into 3 seperate parts? >> >> example: >> John W Smith >> Lisa Marie Jones >> >> I would like the the result to be 3 different fileds: First, Middle, Last >> Name >> >> Is this even possible? >> > > post via the From: field in the message header? Tsk, Tsk. These newsgroups are provided by Microsoft to allow enthusiasts to assist beginners (and each other), not as a shop-front. Phil, London I know I have... he will attempt to get business by any means possible.
-- Show quoteHide quoteGina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Philip Herlihy" <bounceb***@you.com> wrote in message news:ewzPjkv%23JHA.3320@TK2MSFTNGP04.phx.gbl... > Steve wrote: >> It's not foolproof because of the structure of certain names but in your >> example you can use the Instr function to look for spaces to separate the >> name into parts. Look at the Instr function in the Help file. >> >> Steve >> >> >> "KrispyData" <KrispyD***@discussions.microsoft.com> wrote in message >> news:993CB824-1FF9-4A4C-BDFE-0C89207DAD24@microsoft.com... >>> I have a Name field that I would like to divide into 3 seperate parts? >>> >>> example: >>> John W Smith >>> Lisa Marie Jones >>> >>> I would like the the result to be 3 different fileds: First, Middle, >>> Last >>> Name >>> >>> Is this even possible? >>> >> >> > > Anyone else noticed that our old "friend" Steve is advertising in every > post via the From: field in the message header? > > Tsk, Tsk. > > These newsgroups are provided by Microsoft to allow enthusiasts to assist > beginners (and each other), not as a shop-front. > > Phil, London  
Other interesting topics
Linking form information to specific tables
junction table setup Starting Point for Access 2003 database Report in Legal size Table Set-up How do I set up a column to populate... loading Access when I already have a 2007 student Office pkg loade BIRTHDAY MAIL SHOT multiple users updating the same table and sharing Daily download |
|||||||||||||||||||||||
