can i separate a name field into 2 separate columns?

[lausch1973]

I have a database that the name field has a person or business' complete
name...ex. Smith, John or ABC Vineyards. Is there a way that I can have a
query to separate the first name and last name into two separate fields with
business name under the last name field? PLEASE help me!!

 

[Dale Fye]

Take a look at the thread labeled "Parsing a Text String into 4 different
fields", 3/31/09 in the microsoft.public.access.modulesdaovba discussion group

 

[KARL DEWEY]

What do you mean by "with business name under the last name field"?

 

[John Spencer MVP]

IF the name field always as Last Name comma space First Name for individuals
and NEVER has business names with a comma space then you can do this
and ALWAYS has a value.

So a name like ABC Vineyards, Inc. could not be in your database.

Assuming that your Name field follows the above three rules.

LastName: Left([Name],Instr(1,[Name] & ", ",", ")-1)

FirstName: IIF([Name] Like "*, *",Mid([Name],Instr(1,[Name],", ")+ 2),Null)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

I have a database that the name field has a person or business' complete
name...ex. Smith, John or ABC Vineyards. Is there a way that I can have a
query to separate the first name and last name into two separate fields with
business name under the last name field?

How do you propose to tell the difference between a business
name and the other names?

Generally, it is impossible to determine first and last
names. Consider names like Mary Jane Smith Jones,
Oscar de la Hoya. and a gazillion other variations.

 

[lausch1973]

I would like to have the single field split into two fields. I would like it
to have First Name field and Last Name field. I mean that when there is data
with no comma, I would like to have it put into the Last Name field. For
example: Smith, John; ABC Vineyards; Able, Annette would split into: First
Name field containing John and Annette and the Last Name field contain Smith,
ABC Vineyards, and Able.

 

[KARL DEWEY]

BACKUP DATABASE BACKUP DATABASE

Add LastName field and run update query using John Spencer's formula. Then
run his second update query on the name field. Rename to FirstName.

 

[broncojim]

John, I found this formula very helpful. It almost does exactly what I
need. I have about 100 asset types stores in a field.

Examples are below:

A1D 02S
KV2c 16S E
CENTRON 2S
J4S 8KRM

What I am looking to return in a query is "Form" and "2S", "8S" or "16S".

I modified your formula for first name like this:

SIZE: "Form " & IIf([ASSET_TYPE] Like "*
*",Mid([ASSET_TYPE],InStr(1,[ASSET_TYPE]," ")+2),Null))

I replaced the commas betwen the quotes with spaces. Almost all of the
values returned were what I was hoping for.

My question is how do I limit the values returned to only "Form" plus 2 OR
3 characters, ie "Form 2S" or "Form 8K" or "Form 16S"?

Thanks,

Jim

IF the name field always as Last Name comma space First Name for individuals
and NEVER has business names with a comma space then you can do this
and ALWAYS has a value.

So a name like ABC Vineyards, Inc. could not be in your database.

Assuming that your Name field follows the above three rules.

LastName: Left([Name],Instr(1,[Name] & ", ",", ")-1)

FirstName: IIF([Name] Like "*, *",Mid([Name],Instr(1,[Name],", ")+ 2),Null)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a database that the name field has a person or business' complete
name...ex. Smith, John or ABC Vineyards. Is there a way that I can have a
query to separate the first name and last name into two separate fields with
business name under the last name field? PLEASE help me!!

 

[John Spencer MVP]

You might try the number of characters argument for the mid function and use
the trim function to strip off any leading or trailing spaces.

SIZE: "Form " & IIf([ASSET_TYPE] Like "* *",
Trim(Mid([ASSET_TYPE],InStr(1,[ASSET_TYPE]," ")+2,3)),Null)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John, I found this formula very helpful. It almost does exactly what I
need. I have about 100 asset types stores in a field.

Examples are below:

A1D 02S
KV2c 16S E
CENTRON 2S
J4S 8KRM

What I am looking to return in a query is "Form" and "2S", "8S" or "16S".

I modified your formula for first name like this:

SIZE: "Form " & IIf([ASSET_TYPE] Like "*
*",Mid([ASSET_TYPE],InStr(1,[ASSET_TYPE]," ")+2),Null))

I replaced the commas betwen the quotes with spaces. Almost all of the
values returned were what I was hoping for.

My question is how do I limit the values returned to only "Form" plus 2 OR
3 characters, ie "Form 2S" or "Form 8K" or "Form 16S"?

Thanks,

Jim

IF the name field always as Last Name comma space First Name for individuals
and NEVER has business names with a comma space then you can do this
and ALWAYS has a value.

So a name like ABC Vineyards, Inc. could not be in your database.

Assuming that your Name field follows the above three rules.

LastName: Left([Name],Instr(1,[Name] & ", ",", ")-1)

FirstName: IIF([Name] Like "*, *",Mid([Name],Instr(1,[Name],", ")+ 2),Null)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a database that the name field has a person or business' complete
name...ex. Smith, John or ABC Vineyards. Is there a way that I can have a
query to separate the first name and last name into two separate fields with
business name under the last name field? PLEASE help me!!