can i separate a name field into 2 separate columns?

  • Thread starter Thread starter lausch1973
  • Start date Start date
L

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!!
 
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
 
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
 
lausch1973 said:
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?
Click to expand...

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



John Spencer MVP said:
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!!
Click to expand...
Click to expand...
 
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



John Spencer MVP said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top