Ssorting a column of names

[Ray K]

If the contents of each cell in the column is a First Name followed by a
Last Name, is there a way to sort on the Last Name? In other words, make
Access ignore the First Name and the space that follows it, and then
start the sorting.

Thanks,

Ray

 

[Tom Wickerath]

Hi Ray,

Any such algorithm that you might come up with would need to allow for last
names that include a space as well. Honestly, you will be a lot better off to
permanantly split the field in question into two or more fields. This way,
you can apply appropriate indexing, so as to avoid a full table scan. This is
especially important in the table in question has lots of records, and a
network separates you from your data.

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

Update: 5-Nov-2006 for Name data in the format: LastName, FirstName MI
(i.e. comma in-between Lastname and Firstname, but a space in-between
Firstname and MI. In this case, the user did not want to retain the middle
initial).

http://groups.google.com/group/micr..._frm/thread/aae22c59e5d218eb/8168efa560d04200

Field: LastName
Table: Your tablename
Update To: SplitName([Name],Chr(44),0)

Field: FirstName
Table: Your tablename
Update To: SplitName([Name],Chr(32),1)


Option Compare Database
Option Explicit
Public Function SplitName(strFullName As String, _
strCharCode As String, intElement As Integer) As String
On Error Resume Next
Dim strResult() As String
strResult = Split([strFullName], strCharCode)
SplitName = strResult(intElement)

End Function



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

PS. In the first example with VBA code that I gave you, you would want to
switch the 0 and 1, since your names are in the opposite format (Firstname
{space} Lastname). Use this instead:

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

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


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html

 

[Ray K]

PS. In the first example with VBA code that I gave you, you would want to
switch the 0 and 1, since your names are in the opposite format (Firstname
{space} Lastname). Use this instead:

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

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


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html

Thanks, Tom. It's more complicated than I thought. While I used to
program in machine language and Fortran, I haven't kept up with the VBA
code. So maybe I'll just leave things as they are.

For what it's worth, it's a 3000-record song database. The names are
those of the artists. Most of the names are like Miller, Glenn. But some
are Miller Orch, Glenn. The names aren't always in the simple LN comma
space FN format. Then there are those artists with just one name
(Madonna) to complicate things, but at least there are none with middle
initials.

There are no networks involved; everything is on my desktop computer.

Ray