Extract text from field

  • Thread starter Thread starter Mary M
  • Start date Start date
M

Mary M

If you have a filed that contatins the following data:

LastName, FirstName

Where LastName has varing lengths. Can you run an update query to obtain
just the LastName part of the field? If so, what would be the command?

Many, many thanks in advance.
 
Hi Mary

This is a copy of an answer i gave last night to the same question

_______________________________________________

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
 
If you have a filed that contatins the following data:

LastName, FirstName

Where LastName has varing lengths. Can you run an update query to obtain
just the LastName part of the field? If so, what would be the command?

Many, many thanks in advance.
Click to expand...

Sure. Update LastName to

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

and FirstName to

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

John W. Vinson [MVP]
 
Back
Top