Remove Middle name

[Guest]

I'm looking for an advice on how to remove the Middle name. For example, I
have 3 names listed as:
Jimmy Smith K.
Fred Taylor
Mark Brunell M

I would like to have the name listed First and Last name only, such as:
Jimmy Smith
Fred Taylor
Mark Brunell

Please help! Many thanks.

 

[BruceM]

If that is the invariable format (middle initial at the end) you could do
something like this as a calculated query field:

No_MI: IIf(Right([NameField],2) = " ",Left([NameField],Len([NameField] -
2,[NameField])

If some middle initials have periods and some do not, you could do:

No_MI: IIf(Right([NameField],1) = ".",Left([NameField],Len([NameField] -
3,IIf(Right([NameField],2) = " ",Left([NameField],Len([NameField] -
2,[NameField]))

The latter expression tests to see if the rightmost character is a period.
If it is, the expression subtracts 3 from the total length of the string (in
the case of Jimmy Smith K., the total length is 14; subtract 3 to get 11.
The 11 leftmost characters are "Jimmy Smith". If there is no period, the
expression check the second character from the right. If it is a space, 2
is subtracted instead of 3. If neither condition (a period at right or a
space second from the right) applies, the expression returns the original
field.

You will probably also want to separate first and last names into separate
fields. There is more information here:
http://www.mvps.org/access/strings/str0001.htm

In any case, you can run the query as a make-table query in order to include
the new field (or fields) in place of the old one.

 

[Rick B]

Well, you actually have a bigger problem. You should be trying to get this
into separate fields. Each field should contain only one piece of data. In
your case, you have first name, last name, and middle name all in one field.

I would recommend creating three fields (or two if you want to get rid of
middle.) You would then have to manually separate the data, or use some
update queries to try to parse out the data. If your data is ALWAYS in the
format you indicate, you could remove the first name by grabbing everything
up to the first space (you can read the tons of previous posts on the exact
way to create that query).

You would then have to pull the last name out. It might take two steps
(grab everything to the right of the first space, then remove the middle by
again looking for a space.)

Here's the problem... What about:
Anna de los Soto?
Billy Ray Syrus?
John Mc Daniel?
Tom Jones Jr.? (Jr. is not a middle name)

Lots of possible exceptions to your data that may mean it is easier to
manually clean these up.

 

[Guest]

The best way to do it is to take the time to seperate the names into
FirstName, MI, and LastName fields. Then you can mix and match them in any
way that you want later. This is usually best done by a human although you
might get the computer to take a rough swipe at it.

Let's say that you do just want to strip off the MI. Even your small example
shows some problems. Some people don't have middle initials. On one you have
a period where the other does not. There is the second space that might help
however that fails when you get to someone like Tom von Lehman. Then there is
probably dirty data where someone put in the name like Tom T. Kuhl. Unless
you know exactly where the middle initial is always going to be, this is
something best left to a human.

 

[Guest]

The query is in case someone want to look-up in the future:
IIf(InStr([Name],"."),Left([Name],InStr(1,[Name],".")-3),[Name]) AS NewName

 

[Guest]

Never mind. I play around with InStr() and got the answer.

 

[BruceM]

Then why not post what you did? Several people went to the trouble of
posting replies. Now it's your turn, so that others may benefit.