On Fri, 6 Jan 2006 13:19:02 -0800, Sillie_J wrote:

Perfect!! Thank you!!!! Now I just have to figure out how to put the "Jr."
that occurs after the first name into the last name field after the last name
(e.g., Smith,John Jr.) Whoever created this db table before was not
well-versed... I should be able to figure it out based on the expressions
available on the website you provided. Thanks again.

Jill

"freakazeud" wrote:

Hi,
check this:
http://support.microsoft.com/default...b;en-us;209045

It shows you sample expressions to do exactly what you need.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps -
http://www.oli-s.de

"Sillie_J" wrote:

I am working with Access tables given to me by a client and incorporating
their data into a larger database. The old tables have the first name and
last name in one field, which most of us know it's a no-no in database world.
The last name is first, followed by a comma (no space), then the first name.
Here's an example, "Smith,John".

How can I separate the last name into its own field and likewise for the
first name? The lengths of the names vary, so it's not possible to specify
the number of characters in using Left, Mid, or Right coding. Is there a way
to use the comma as a separator?

Thank you!

If you are using Access 2000 or newer, look up InStrRev() in VBA Help.

Title:
IIf(InStr([CombinedNames],"Jr."),Mid([CombinedNames],InStrRev([CombinedNames],"
")+1),"")

The above will work even if the First Name position includes a space,
i.e.
Sands,Henry Alan Jr.
Sands,H. S. Jr.

Is Jr. the only title you have? What about Sr., Dr., PhD., Ms., etc.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail