Expresson Help

[Guest]

What do i do if some FullName's have MiddleName's and some don't?
Ex.
Doe, John A
Smith, Joe E
Albert, Sam

1) Create the 3 new fields in the table.

2) Run an update query to place the correct portion of the combined name
into each field. If there are first and middle names with spaces in them, it
may cause a problem. If it is just one or the other, you would just find the
space form the other end of the string. Spaces in the last name can be
handled by using the comma as the divider.

Example:
UPDATE Table1 Set Table1.FirstName = Mid([FullName], Instr([FullName], ",")
+ 2, InStrRev([FullName], " ") - (Instr([FullName], ",") + 2)),
Table1.MiddleName = Mid([FullName], InStrRev([FullName], " ")+ 1),
Table1.LastName = Left([FullName], InStr([FullName], ",") - 1);

Replace FullName with the name of the field that has the full name. As
mentioned earlier, depending on spaces within the name, you may need to
modify some of the above. I assumed no middle names with spaces in the query
above. First or last names with spaces will work.

--
Wayne Morgan
MS Access MVP

I have a column that contains names in the following format - Doe, John A
I need to divide this name into 3 seperate columns - First, Last, &
Middle.

 

[John Vinson]

What do i do if some FullName's have MiddleName's and some don't?
Ex.
Doe, John A
Smith, Joe E
Albert, Sam

If it's a one-time operation I'd suggest doing it in two passes.
First, update LastName to

Left([fullname], InStr([fullname], ",") - 1)

and FirstName to

Trim(Mid([fullname], InStr([fullname], ",") + 1))

Then in a second query use a criterion on FirstName of

LIKE "* *"

to limit the operation to those with middlenames; update Firstname to

Left([Firstname], InStr([Firstname], " ")-1)

and Middlename to

Trim(Mid([FirstName], InStr([FirstName], " "))


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps