Sorting by Last Name and then First Name in the Same Field

[RS]

I have "inherited" a database where the first name and last name are
contained in the same field.

1. Is there any way to separate these two in items into two fields?

2. Is there any way to sort in the same field by last name and then first
name? I can only get it to sort by the first name since the first name is
first in the field.

Any assistance would be much appreciated!!

 

[fredg]

I have "inherited" a database where the first name and last name are
contained in the same field.

1. Is there any way to separate these two in items into two fields?

2. Is there any way to sort in the same field by last name and then first
name? I can only get it to sort by the first name since the first name is
first in the field.

Any assistance would be much appreciated!!

The names are ALL in FirstName space LastName order, i.e. John Smith?
In a query?
FirstName:Left([FullName],InStr([FullName]," ")-1)

LastName:Mid([FullName],InStr([FullName]," ")+1)

You can then sort your report on the LastName field, FirstName field,
using the Report's Sorting and Grouping dialog.

The above will fail, however, if the names are like Joseph Allen
Gould.

To actually separate the data into 2 different fields in your table
(which is the correct way), then create an Update query after first
adding the FirstName and LastName fields to the table.

Here is the SQL needed. Change the table and field names to your
actual table and field names.

Update YourTable set YourTable.FirstName =
Left([FullName],InStr([FullName]," ")-1), YourTable.LastName =
Mid([FullName],InStr([FullName]," ")+1);

But you must first make sure all the data is in the correct FirstName
space LastName order.

 

[RS]

Thank you, Fred. I appreciate the assistance!

I have "inherited" a database where the first name and last name are
contained in the same field.

1. Is there any way to separate these two in items into two fields?

2. Is there any way to sort in the same field by last name and then first
name? I can only get it to sort by the first name since the first name is
first in the field.

Any assistance would be much appreciated!!

The names are ALL in FirstName space LastName order, i.e. John Smith?
In a query?
FirstName:Left([FullName],InStr([FullName]," ")-1)

LastName:Mid([FullName],InStr([FullName]," ")+1)

You can then sort your report on the LastName field, FirstName field,
using the Report's Sorting and Grouping dialog.

The above will fail, however, if the names are like Joseph Allen
Gould.

To actually separate the data into 2 different fields in your table
(which is the correct way), then create an Update query after first
adding the FirstName and LastName fields to the table.

Here is the SQL needed. Change the table and field names to your
actual table and field names.

Update YourTable set YourTable.FirstName =
Left([FullName],InStr([FullName]," ")-1), YourTable.LastName =
Mid([FullName],InStr([FullName]," ")+1);

But you must first make sure all the data is in the correct FirstName
space LastName order.