I use Access 2000. In my database I have a field called Full_Name
where a user is to type the full name of a contact. Consequently,
this could be filled in as "Mr. William M. Jones, III". I need a way
for the program to extract the last name (Jones) so I can alphabetize.
Does anyone know how I can do this?

Access has a variety of string functions that you can use to analyze the
content of text fields. Depending on the # of records and your time
available, you might be able to take the existing strings apart and
spread the data out over several fields.

Suggested fields(based on Mr. Jones):
Prefix, FirstName, MiddleName, LastName, Suffix

Below I'll mention a handful of useful functions. Look them up in help
to get more complete details on each.

One function that you will likely need is IIf(), which provides an IF
logic test with 2 possible result paths.

Here's one function you will need:
QueryField: InStr(1,[Full_Name],"Mr")
will give result of 1 as that is the location of the
start of "Mr" in that record.

You can use InStr() to find the location of the space between words,
then use that value to omit the word before and show the rest, with Mid().

FoundPrefix: Mid([Full_Name],1,3)
will give "Mr.". It starts at position #1
in the field and gives 3 characters
There are also functions Left() and Right().

Wildcard characters can help in finding what you need.

Bruce Pick
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++

Bill Engel wrote: