Name Field

[Guest]

Hello All,

I have inherited a database that contains a table with a Column called
'name' where the formatting is (Lname, Fname) ex: Jones, Mary. Is there a way
I can split this into two different columns easily-One column named FName and
another named LName? IS there a command that does this? There are many many
records so it would be helpful if this were possible. Thanks so much!

 

[Al Camp]

lxpoetess,
Well, first you'll have to make sure that ALL the Names are really just a
Last, a comma, and a First. If any have middle initials, or some other "non
Last, First value, then "one cure won't fit all"
Add two fields to your table... LastName and FirstName.
Now you'll need to set up an Update query to extract the "Last" part of
Name and put it into the new LastName field.

Update LastName with this criteria...
Left(Name, InStr(",") -1
That will extract all the characters to the left of the comma, and update
LastName accordingly

Now do the same for the FirstName field, but with this criteria...
Mid(Name, InStr(",") +1)
That will extract all the characters to the right of the comma, and
update FirstName accordingly.

 

[Guest]

Hi.

First, "Name" is a reserved word in Access. If you are keeping this field
after the split, you should rename it to something like "FullName" to avoid
future problems.

To do the split, and assuming that each Name contains only one comma that
separates the first and last names, the following query should work (be sure
to substitute your actual table name for "TableName"):

SELECT Left([Name],InStr(1,[Name],",")-1) AS LName,
Right([Name],Len([Name])-InStr(1,[Name],",")-1) AS FName
FROM TableName;

-Michael

 

[Guest]

A few other alternatives, to those you have already received...

One of my favorite utilities for splitting names in Access is called
"Splitter for Microsoft Access". If you don't mind popping $39 for a copy,
you will have a very good utility that can untangle the messiest name data:

http://www.infoplan.com.au/splitter/

A very easy method to use involves exporting the table to Excel. From within
Excel, use Data > Text to Columns... Then re-import your table back into
Access.

You can also create the new fields in the table and then use an update query
to populate the new fields. The update query would look like this:

Field: LastName
Table: Your tablename
Update To: SplitName([FullName],0)

Field: FirstName
Table: Your tablename
Update To: SplitName([FullName],1)

Field: MiddleInitial
Table: Your tablename
Update To: SplitName([FullName],2)

Create a new standard module. Then copy and paste the following function
into it. This will create a zero-based array named strResult. The On Error
Resume Next statement will prevent the function from choking to a halt if
there is no middle name for a given record.

Public Function SplitName(FullName As String, _
intElement As Integer) As String
On Error Resume Next

Dim strResult() As String
strResult = Split([FullName], Chr(32))

SplitName = strResult(intElement)

End Function


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi guys, thanks for the tips. But for some reason it is giving an "invalid
procedure call error". when I try to run the update query....any ideas? Sorry
to rack you guys' brains! Thanks!

~Luciana

Hi.

First, "Name" is a reserved word in Access. If you are keeping this field
after the split, you should rename it to something like "FullName" to avoid
future problems.

To do the split, and assuming that each Name contains only one comma that
separates the first and last names, the following query should work (be sure
to substitute your actual table name for "TableName"):

SELECT Left([Name],InStr(1,[Name],",")-1) AS LName,
Right([Name],Len([Name])-InStr(1,[Name],",")-1) AS FName
FROM TableName;

-Michael

Hello All,

I have inherited a database that contains a table with a Column called
'name' where the formatting is (Lname, Fname) ex: Jones, Mary. Is there a way
I can split this into two different columns easily-One column named FName and
another named LName? IS there a command that does this? There are many many
records so it would be helpful if this were possible. Thanks so much!

 

[Al Camp]

lxpoetess,
Access could be balking on the field name "Name" (a reserved word)
Go to your table and rename Name to OldName, and then use that in your
update query.

If you still have trouble, cut and paste your SQL into your post.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hi guys, thanks for the tips. But for some reason it is giving an "invalid
procedure call error". when I try to run the update query....any ideas?
Sorry
to rack you guys' brains! Thanks!

~Luciana

Hi.

First, "Name" is a reserved word in Access. If you are keeping this
field
after the split, you should rename it to something like "FullName" to
avoid
future problems.

To do the split, and assuming that each Name contains only one comma that
separates the first and last names, the following query should work (be
sure
to substitute your actual table name for "TableName"):

SELECT Left([Name],InStr(1,[Name],",")-1) AS LName,
Right([Name],Len([Name])-InStr(1,[Name],",")-1) AS FName
FROM TableName;

-Michael

Hello All,

I have inherited a database that contains a table with a Column called
'name' where the formatting is (Lname, Fname) ex: Jones, Mary. Is there
a way
I can split this into two different columns easily-One column named
FName and
another named LName? IS there a command that does this? There are many
many
records so it would be helpful if this were possible. Thanks so much!