Need macro to reverse a name in one field T Gill 25.06.2006 23:37:01 I'm sort of a novice macro developer but I really need help from someone concerning reversing a name. I have a long list of names in one field that are in last, first middle initial (sometimes without the middle initial and sometimes the whole middle name) order and I need it reversed to just first name and one space and then last name to make a mailing list. Unfortunately, this data is one field and not separate fields and this is the only way I can get the data. It is a long list to have to retype so I was hoping there might be a macro or something that counld help me reverse the name. I get the data in Excel then I move it to Access so if there is something in Excel that is is not in Access that would be fine. Please Help! Thanks!

Re: Need macro to reverse a name in one field Steve Schapel <schapel[ at ]mvps.org.ns> 26.06.2006 02:50:36 T Gill, This is not really a job for a Macro, it is a Query you need. I would recommend adding a new field to the table to take the amended version, and then using an Update Query to move the transposed names into it. It is theoretically possible to just base your mail outs directly on a query that transposes the names, but in practice I expect you will probably need to go through and manually tweak some of them. This is because there are always exceptions, for example people with a "two name first name" like Peggy Sue. So, I understand from your question that the existing data has a comma after the last name, so that is handy. So, try updating the new field to the equivalent of this (untested!)... Mid([YourNameField],InStr([YourNameField],",")+2,InStr([YourNameField]," ")-InStr([YourNameField],",")) & " " & Left([YourNameField],InStr([YourNameField],",")-1) -- Steve Schapel, Microsoft Access MVP T Gill wrote: [Quoted Text] > I'm sort of a novice macro developer but I really need help from someone > concerning reversing a name. I have a long list of names in one field that > are in last, first middle initial (sometimes without the middle initial and > sometimes the whole middle name) order and I need it reversed to just first > name and one space and then last name to make a mailing list. Unfortunately, > this data is one field and not separate fields and this is the only way I can > get the data. It is a long list to have to retype so I was hoping there > might be a macro or something that counld help me reverse the name. I get > the data in Excel then I move it to Access so if there is something in Excel > that is is not in Access that would be fine. Please Help! Thanks!

Re: Need macro to reverse a name in one field T Gill 27.06.2006 21:08:01 Thanks for the information. I guess it needs a little tweaking. When I run the query with this as the Criteria under my new field I get an Invalid Procedure Call. I'm not familiar with such code so any help would be appreciated. I don't have my data yet but I just set up a simple access database with two entries with the name in Last, First Middle format in the same field. Thanks so much for your help!! "Steve Schapel" wrote: [Quoted Text] > T Gill, > > This is not really a job for a Macro, it is a Query you need. > > I would recommend adding a new field to the table to take the amended > version, and then using an Update Query to move the transposed names > into it. It is theoretically possible to just base your mail outs > directly on a query that transposes the names, but in practice I expect > you will probably need to go through and manually tweak some of them. > This is because there are always exceptions, for example people with a > "two name first name" like Peggy Sue. > > So, I understand from your question that the existing data has a comma > after the last name, so that is handy. > > So, try updating the new field to the equivalent of this (untested!)... > > > Mid([YourNameField],InStr([YourNameField],",")+2,InStr([YourNameField]," > ")-InStr([YourNameField],",")) & " " & > Left([YourNameField],InStr([YourNameField],",")-1) > > -- > Steve Schapel, Microsoft Access MVP > > T Gill wrote: > > I'm sort of a novice macro developer but I really need help from someone > > concerning reversing a name. I have a long list of names in one field that > > are in last, first middle initial (sometimes without the middle initial and > > sometimes the whole middle name) order and I need it reversed to just first > > name and one space and then last name to make a mailing list. Unfortunately, > > this data is one field and not separate fields and this is the only way I can > > get the data. It is a long list to have to retype so I was hoping there > > might be a macro or something that counld help me reverse the name. I get > > the data in Excel then I move it to Access so if there is something in Excel > > that is is not in Access that would be fine. Please Help! Thanks! >

Re: Need macro to reverse a name in one field Steve Schapel <schapel[ at ]mvps.org.ns> 27.06.2006 21:27:49 T Gill, No, it doesn't go in the Criteria. Make the query an Update Query by selecting Update from the Query menu. Then, the expression I gave you needs to go in the Update To row of the query design grid. It all goes on one line, not like the newsreader has probably word-wrapped it. And you need to replace the 'YourNameField' in the expression with the actual name of your name existing name field. Hope that clarifies. -- Steve Schapel, Microsoft Access MVP T Gill wrote: [Quoted Text] > Thanks for the information. I guess it needs a little tweaking. When I run > the query with this as the Criteria under my new field I get an Invalid > Procedure Call. I'm not familiar with such code so any help would be > appreciated. I don't have my data yet but I just set up a simple access > database with two entries with the name in Last, First Middle format in the > same field. Thanks so much for your help!! >

Re: Need macro to reverse a name in one field T Gill 27.06.2006 22:41:02 Hey, this works pretty well. The only problem is that I only have the first letter of the first name but I do have the full last name after that one letter first name. Any ideas on how to get the rest of the first name and middle name after that comma? Thanks for being so helpful! I would have never figured this out on my own. "Steve Schapel" wrote: [Quoted Text] > T Gill, > > No, it doesn't go in the Criteria. Make the query an Update Query by > selecting Update from the Query menu. Then, the expression I gave you > needs to go in the Update To row of the query design grid. It all goes > on one line, not like the newsreader has probably word-wrapped it. And > you need to replace the 'YourNameField' in the expression with the > actual name of your name existing name field. Hope that clarifies. > > -- > Steve Schapel, Microsoft Access MVP > > T Gill wrote: > > Thanks for the information. I guess it needs a little tweaking. When I run > > the query with this as the Criteria under my new field I get an Invalid > > Procedure Call. I'm not familiar with such code so any help would be > > appreciated. I don't have my data yet but I just set up a simple access > > database with two entries with the name in Last, First Middle format in the > > same field. Thanks so much for your help!! > > >

Re: Need macro to reverse a name in one field Steve Schapel <schapel[ at ]mvps.org.ns> 27.06.2006 23:24:54 T Gill, Ah, sorry, I was trying to do it off the top of my head, and forgot about the first space after the comma. Please try it like this, and let me know... Mid([YourNameField],InStr([YourNameField],",")+2,InStr(Mid([YourNameField],InStr([YourNameField],",")+2)," ")-1) & " " & Left([YourNameField],InStr([YourNameField],",")-1) -- Steve Schapel, Microsoft Access MVP T Gill wrote: [Quoted Text] > Hey, this works pretty well. The only problem is that I only have the first > letter of the first name but I do have the full last name after that one > letter first name. Any ideas on how to get the rest of the first name and > middle name after that comma? Thanks for being so helpful! I would have > never figured this out on my own.