Sorting

  • Thread starter Thread starter Haggr1 via AccessMonster.com
  • Start date Start date
H

Haggr1 via AccessMonster.com

For whatever reason, 7 years ago when setting up my main table, I have made a
field "SetterCompany". It has has names like <John Smith; June Brown>

There is no way I can go back and change this field, after hundreds Queries
and 10+ forms

Question: How can I sort by last name.
 
There is no way to do it efficiently with your current design.
I regularly make far bigger changes that you are contemplating regarding
redesigning the name field in this table.
An alternative would be to create a new column containing just the last name
then you could sort on that.

-Dorian
 
IF the names are ALWAYS First Name space Last Name and never something like
Mary Anne Josephson:

Build a calculated field as follows. This should return everything after
the first space. If there is no space, it will return the entire value in
SetterCompany.
Field: GetLastName: Mid(SetterCompany, Instr(1,SetterCompany," ") +1)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top