Separating Name

[Guest]

Hello. I have a field that contains a persons last name a comma and then the
persons first name. Last name and First name is always separated with a
comma in the first field. I want to be able to have a query that has that
field and then 2 additional fields, one with the first name and one with the
last name. For instance the first field says: "Petterson, Mary" I want a
query that has that in the first field and then "Petterson" in the second
field and "Mary" in the third. Can this be done easily?
TIA

 

[Guest]

Hi Jenn

Create a query and bring the table containing the full name in to the top
section

Column 1 (not really neeed but to will help spot any errors) put this
FullName: [TableName]![NameOfField]

Column 2 put this
1stName:Left( [TableName]![NameOfField],InStr(1,
[TableName]![NameOfField],",")-1)


Column3 put this
2ndName: Right(Trim( [TableName]![NameOfField]),Len(Trim(
[TableName]![NameOfField]))-InStr(1, [TableName]![NameOfField]," "))


Change
TableName to what is it is
NameOfField to the name of the field containing the name and commas.

Hope this helps

 

[Douglas J. Steele]

SELECT FullName, Left([FullName], InStr([FullName], ", ") -1) AS LastName,
Trim(Mid([FullName], InStr([FullName], ", ") +1)) AS FirstName

 

[Katrina]

I would do as the others have stated. But keep one thing in mind suffixes.
They are normally entered after a comma. Example: Johnson, Jr, William
If your data does not have suffixes entered in this manner then you should
be fine. Just a note from my experience.

Kat

 

[Guest]

Thanks for the fast response everyone! Works great!