Expression Help

[jackel]

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.

 

[Wayne Morgan]

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.

 

[jackel]

Thanks alot that worked.

Now 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.

 

[Wayne Morgan]

You could use the following in an IIf statement to determine if the first
space is the one after the comma. If the first name has spaces in it, this
won't work.

Mid("Doe, John", InStrRev("Doe, John", " ") - 1, 1) = ","

The equation in the last message would be the False part of the IIf, you
would leave out the part for the middle name in the True part of the IIf.

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

This is untested, I think I got all of the parenthesis paired up.

--
Wayne Morgan
MS Access MVP

Thanks alot that worked.

Now 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.

 

[Wayne Morgan]

Disregard the previous. It is close, but I put the Field name inside the
IIf, it needs to go outside of it. I'm leaving for work and will reply again
later.

--
Wayne Morgan
MS Access MVP

Thanks alot that worked.

Now 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.

 

[Wayne Morgan]

Corrected, but still untested. If you have the middle name field set to
accept zero length strings but not Nulls, then change Null to "".

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


--
Wayne Morgan
Microsoft Access MVP

Thanks alot that worked.

Now 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.

 

[jackel]

Thank You!!!

Corrected, but still untested. If you have the middle name field set to
accept zero length strings but not Nulls, then change Null to "".

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


--
Wayne Morgan
Microsoft Access MVP

Thanks alot that worked.

Now 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.