Need macro to reverse a name in one field

[T Gill]

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!

 

[Steve Schapel]

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)

 

[T Gill]

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!!

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

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!

 

[Steve Schapel]

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.

 

[T Gill]

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]

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)