Sometimes there a middle initial

[Lori]

Okay, I've looked at the previous questions concerning splitting full names.
However, Our employee listing will sometimes list a name with a middle
initial. It's not consistent. How would I separate out a field that has a
combination of data?

From what I've reviewed the names are listed
1. Last Name,First Name
2. Last Name,First Name MI(space)(followed by a period)
3. Last Name,First Name(space)MI (no period)

I can obviously get rid of the periods in a mass search and replace before I
import the spreadsheet but how to I get rid of the middle initial (when its
not consistent) so that I can just deal with first and last names?

HELP!

 

[KARL DEWEY]

After removing the period check for initial like this --
Len(Trim(Right([YourField],2)))
criteria = 1
I have never heard of first name that is one character but you may have a
problem with name like J. Paul Getty loaded as Getty, J. Paul in the field.

 

[fredg]

Okay, I've looked at the previous questions concerning splitting full names.
However, Our employee listing will sometimes list a name with a middle
initial. It's not consistent. How would I separate out a field that has a
combination of data?

From what I've reviewed the names are listed
1. Last Name,First Name
2. Last Name,First Name MI(space)(followed by a period)
3. Last Name,First Name(space)MI (no period)

I can obviously get rid of the periods in a mass search and replace before I
import the spreadsheet but how to I get rid of the middle initial (when its
not consistent) so that I can just deal with first and last names?

HELP!

Why are your users allowed to enter the full name in a single cell,
even in Excel? Certainly a more efficient spreadsheet entry method
would be 3 separate columns for LastName, FirstName, and Initial.
Then you wouldn't need to do any splitting.

Names are usually stored with a space after the comma, such as:
Smith, Mark
van Beethoven, Ludwig
Jones, Joseph P
Collins, William L.

SELECT Left([CombinedNames],InStr([CombinedNames],",")-1) AS LastName,
IIf(InStrRev([CombinedNames]," ")>InStr([CombinedNames],",
")+2,Mid([CombinedNames],InStr([CombinedNames],",
")+2,InStrRev([CombinedNames]," ")-1-InStr([Combinednames],",
")-1),Mid([CombinedNames],InStr([CombinedNames],", ")+2)) AS FirstName
FROM tblBasicData
WHERE (((tblBasicData.Combinednames) Is Not Null));

The above will return the LastName and FirstName only.


If your names are stored without the comma space, (Jones,Joseph P) a
simple methord to use the above query would be to first update the
field to add the space:

Update tblBasicData Set tblBasicData.CombinedNames =
Replace([Combinednames],",",", ");

The run the first query.

 

[John Spencer]

Two queries

UPDATE YourTable
SET YourField = LEFT([YourField],Len([YourField])-1)
WHERE YourField like "*.")

UPDATE YourTable
SET YourField = LEFT([YourField],Len([YourField])-1)
WHERE YourField like "* [Az]")



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Lori]

I'm using spreadsheets provided to me from other companies (Nextel/Sprint,
Verizon, etc.) when they convert their data for our cellphones from what ever
format they are using to excel it merges the first and last names (I know
those are separate fields for Sprint) into one. I have no control over how
the field appears when I receive the report from the carrier.
--
Lori A. Pong

Okay, I've looked at the previous questions concerning splitting full names.
However, Our employee listing will sometimes list a name with a middle
initial. It's not consistent. How would I separate out a field that has a
combination of data?

From what I've reviewed the names are listed
1. Last Name,First Name
2. Last Name,First Name MI(space)(followed by a period)
3. Last Name,First Name(space)MI (no period)

I can obviously get rid of the periods in a mass search and replace before I
import the spreadsheet but how to I get rid of the middle initial (when its
not consistent) so that I can just deal with first and last names?

HELP!

Why are your users allowed to enter the full name in a single cell,
even in Excel? Certainly a more efficient spreadsheet entry method
would be 3 separate columns for LastName, FirstName, and Initial.
Then you wouldn't need to do any splitting.

Names are usually stored with a space after the comma, such as:
Smith, Mark
van Beethoven, Ludwig
Jones, Joseph P
Collins, William L.

SELECT Left([CombinedNames],InStr([CombinedNames],",")-1) AS LastName,
IIf(InStrRev([CombinedNames]," ")>InStr([CombinedNames],",
")+2,Mid([CombinedNames],InStr([CombinedNames],",
")+2,InStrRev([CombinedNames]," ")-1-InStr([Combinednames],",
")-1),Mid([CombinedNames],InStr([CombinedNames],", ")+2)) AS FirstName
FROM tblBasicData
WHERE (((tblBasicData.Combinednames) Is Not Null));

The above will return the LastName and FirstName only.


If your names are stored without the comma space, (Jones,Joseph P) a
simple methord to use the above query would be to first update the
field to add the space:

Update tblBasicData Set tblBasicData.CombinedNames =
Replace([Combinednames],",",", ");

The run the first query.

 

[John Spencer]

When you originally posted this I gave you two update queries that
should accomplish this.

If you don't know how to use the SQL statement to make an update query
you should post back and tell us that you don't know how to use the SQL
view and can only build queries in the Design View (the query grid).

Are you importing the spreadsheets or linking to them?

If you are importing the sheets, you can use two update queries to "fix"
the data.

This one should remove the period at the end if there is one.
UPDATE [YourTable]
SET [YourField] = Trim(LEFT([YourField],Len([YourField])-1))
WHERE [YourField] like "*.")

This query should remove any space followed by a Letter at the end if
there is one.
UPDATE [YourTable]
SET [YourField] = LEFT([YourField],Len([YourField])-2)
WHERE YourField like "* [Az]")

To build the first update query
-- Start a new query
-- Select your table
-- Add the Name field to the query grid
-- In the criteria "cell" enter
Like "*."
-- Select Query: Update from the menu
-- In the total cell under the name field type (obviously replace
YourField with the name of your name field)
Trim(LEFT([YourField],Len([YourField])-1))
-- Select Query: Run

If that works the way you want you can save it and modify it as needed
if the tablenames change. Switch to SQL view and see what the query
looks like.

Try building the second query in the same way.


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