Sorting a Name Field

[RS]

I have "inherited" a database where the Name field contains both the first
name and last name in the same field. I am trying to sort the field, but can
only sort it by first name.

Does anyone know of a way to:

#1 - sort this field so it is sorted by last name first; and/or

#2 - separate this field into a first name and last name so I do not have to
go through this every time.

I appreciate any assistance!

 

[Steve Schapel]

RS,

You will be able to do this so you get it right for *most* records, by
splitting the data on the space between the first name and last name.
However, if it is typical of tables of people's names, there will be a
number of examples where the pattern is not followed. Peggy Sue
McAlister, Vincent Van Gogh, J Paul Getty, etc.

Anyway, with that proviso, make a first name field and last name field
in the table. Then make an update query, and update first name to the
equivalent of:
Left([YourNameField],InStr([YourNameField]," ")-1)
... and the last name field to the equivalent of:
Mid([YourNameField],InStr([YourNameField]," ")+1)

And then go through them and look for the odd ones out.

 

[J_Goddard via AccessMonster.com]

Hi -

In any database like this, containing names, it is always good practice to
keep the last name a first name(s) in separate fields, to avoid the problem
you are having. A query can be used to display the name properly.

To answer your questions -

Sorting the list when the names are combined can be done using a query, but
the expressions involved are the same as those used to split the full names
into first and last names, so you might as well split the names anyway.

If we assume that the vast majority of the last names in the data are one
word, then you can do this, in VBA:

Dim P1 as integer ' P1 is a pointer
P1 = instrrev(FullName," ") ' Find the first blank from the right
LastName = mid(Fullname,P1 +1)
FirstName = left(FullName, P1 -1)

A query is a bit more cumbersome in the expressions, but does the same thing,
and could contain expressions like this:

LastName: mid([Fullname],instrrev([FullName]," ") +1)
FirstName: left([FullName], instrrev([FullName]," ") -1)

All you need to do is add two new fields for firstname and lastname to your
table, then put these expressions in an update query.

There are bound to be a few mistakes - it won't work for last names like Van
Gogh, for example. also, first names and middle names or initials will be in
the same field, which may or may not be OK in your case.

I am assuming here that you are reasonably well-versed in the use of VBA or
update queries - if not, just let us know.

HTH

John

 

[RS]

Thank you, John and Steve, for your assistance. I would totally agree that
the names should be separated from the outset. Unfortunately, this was an
"inherited" database. I appreciate the assistance!!

RS

Hi -

In any database like this, containing names, it is always good practice to
keep the last name a first name(s) in separate fields, to avoid the problem
you are having. A query can be used to display the name properly.

To answer your questions -

Sorting the list when the names are combined can be done using a query, but
the expressions involved are the same as those used to split the full names
into first and last names, so you might as well split the names anyway.

If we assume that the vast majority of the last names in the data are one
word, then you can do this, in VBA:

Dim P1 as integer ' P1 is a pointer
P1 = instrrev(FullName," ") ' Find the first blank from the right
LastName = mid(Fullname,P1 +1)
FirstName = left(FullName, P1 -1)

A query is a bit more cumbersome in the expressions, but does the same thing,
and could contain expressions like this:

LastName: mid([Fullname],instrrev([FullName]," ") +1)
FirstName: left([FullName], instrrev([FullName]," ") -1)

All you need to do is add two new fields for firstname and lastname to your
table, then put these expressions in an update query.

There are bound to be a few mistakes - it won't work for last names like Van
Gogh, for example. also, first names and middle names or initials will be in
the same field, which may or may not be OK in your case.

I am assuming here that you are reasonably well-versed in the use of VBA or
update queries - if not, just let us know.

HTH

John

I have "inherited" a database where the Name field contains both the first
name and last name in the same field. I am trying to sort the field, but can
only sort it by first name.

Does anyone know of a way to:

#1 - sort this field so it is sorted by last name first; and/or

#2 - separate this field into a first name and last name so I do not have to
go through this every time.

I appreciate any assistance!