sorting from the middle of the text

  • Thread starter Thread starter BrynB
  • Start date Start date
B

BrynB

I need to sort by last name, but the person who created the database has a
full name field starting with the first name. How do I skip over the first
name to sort by the last name?
 
I need to sort by last name, but the person who created the database has a
full name field starting with the first name. How do I skip over the first
name to sort by the last name?
Click to expand...

You may want to undo this person's mistake just so you can index the last name
field, and resolve anomalies such as "Billy Bob Thornton" (whose last name is
Thornton) vs. "Luis de la Torre" (whose last name is de la Torre, not Torre).

As a temporary expedient create a Query based on your table with a calculated
field. In a vacant Field cell in a query type

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

and sort ascending by this field. It will sort Billy Bob under "Bob" but it
should get most names OK.
 
This worked great. Thank you. Could you tell me how to isolate just the first
name using the same methods?

John W. Vinson said:
I need to sort by last name, but the person who created the database has a
full name field starting with the first name. How do I skip over the first
name to sort by the last name?
Click to expand...

You may want to undo this person's mistake just so you can index the last name
field, and resolve anomalies such as "Billy Bob Thornton" (whose last name is
Thornton) vs. "Luis de la Torre" (whose last name is de la Torre, not Torre).

As a temporary expedient create a Query based on your table with a calculated
field. In a vacant Field cell in a query type

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

and sort ascending by this field. It will sort Billy Bob under "Bob" but it
should get most names OK.
Click to expand...
 
Back
Top