 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / July 2007HOW TO SPLIT INITIALS IN A NAME - my first post - pls help
Dear All I have about 3 thou odd names. The initials, first name, last name all entered in one field. My first task is to sort these names by initials in one field, first name in one field and last name in another field. Pls advice. If all fields contain three parts you can use in a query as new fields Initials: Left([FieldName] , Instr([FieldName]," ")-1) take the string from the beggining until the first space FirstName:Mid([FieldName] , Instr([FieldName]," ")+1, InstrRev([FieldName]," ")-1) take the string from the first space to the second one LastName:Mid([FieldName] , InstrRev([FieldName]," ")+1) Take the string from the last space to the end Note: field that don't includes all three parts won't give you the desire resault  SignatureGood Luck BS"D > Dear All > I have about 3 thou odd names. The initials, first name, last name all > entered in one field. My first task is to sort these names by initials in > one field, first name in one field and last name in another field. Pls advice. D/Cohen--Thanks for giving me a start. I forgot to inform that i am using access-97. I went into querry and in criteria =Left([NAME_AWRY] , Instr([NAME_AWRY],"")-1) . When i run this querry I get procedure error. . pls help > If all fields contain three parts you can use in a query as new fields > [quoted text clipped - 15 lines] > > entered in one field. My first task is to sort these names by initials in > > one field, first name in one field and last name in another field. Pls advice. Why would you put such an expression in the Criteria? That is testing for the value of the field being the result of the expression, and, from your description, if that were the case, it would be an error in your data. Also, the questions that get the most accurate answers are precise and concise, and your description did not state the "delimiters", nor give an example. For other good suggestions on effective use of newsgroups see the FAQ at http://www.mvps.org/access/netiquette.htm. I'd suggest you post the question, with details, clarification, and the example as a followup in hopes that someone can/will offer a useful suggestion. Larry Linson Microsoft Access MVP D/Ofer I also tried putting this in the field area for expressions: Initials: Left([NAME_AWRY],InStr([NAME_AWRY]," ")-1) Unfortunately since the names are without any spaces after the initials, it is giving error for names like A.P.A.PETER GREG. pLS ADV > If all fields contain three parts you can use in a query as new fields > [quoted text clipped - 15 lines] > > entered in one field. My first task is to sort these names by initials in > > one field, first name in one field and last name in another field. Pls advice. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.