sort by last name

[Haggr1 via AccessMonster.com]

I have a field [SetterCompany] ex John Smith. I would like to sort by last
name in a query that is the "Row Source" for a "List Box" I know dividing
the field into 2 fields "[first] [last]" is what should have been done, but I
can't do that now, because they are hundreds of "queries, tables, forms..."
that would have to be changed. Thanks

 

[John W. Vinson]

I have a field [SetterCompany] ex John Smith. I would like to sort by last
name in a query that is the "Row Source" for a "List Box" I know dividing
the field into 2 fields "[first] [last]" is what should have been done, but I
can't do that now, because they are hundreds of "queries, tables, forms..."
that would have to be changed. Thanks

A *partial* solution is to use an expression like

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

in a calculated field in the combo's row source; you can sort by it.

This will give problems with names which don't contain a blank (you could use
an IIF() to handle this), and with names like "Billy Bob Thornton".

 

[Michel Walsh]

Precisely where you are wrong,... maybe



Have a new table with the two fields, and once done, have a query with the
same name than the old table (sure, you will have to rename the old table
before):



SELECT FirstName & " " & LastName As SetterCompany, ...
FROM newTable


The forms and reports will then use that new QUERY, without any problem,
and you will have your new table with its two fields for the name.



Vanderghast, Access MVP

 

[John Spencer]

If (IF) your name field always has a first name, a space, and a last name you
can use a calculated field to do the sorting.

Field: Mid([SetterCompany] & "" ,Instr(1,[SetterCompany]," ")-1)
Sort: Ascending.

That will have problems with names like
Mary Anne Browne
J P Morgan
etc.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Haggr1 via AccessMonster.com]

that returns "Smith" for "Joe Smith" How can I get "Smith Joe"? Aslo
your "Billy Bob Thorton" is applicable, How would that IIF() be written.
Thanks

I have a field [SetterCompany] ex John Smith. I would like to sort by last
name in a query that is the "Row Source" for a "List Box" I know dividing
the field into 2 fields "[first] [last]" is what should have been done, but I
can't do that now, because they are hundreds of "queries, tables, forms..."
that would have to be changed. Thanks

A *partial* solution is to use an expression like

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

in a calculated field in the combo's row source; you can sort by it.

This will give problems with names which don't contain a blank (you could use
an IIF() to handle this), and with names like "Billy Bob Thornton".

 

[John W. Vinson]

that returns "Smith" for "Joe Smith" How can I get "Smith Joe"? Aslo
your "Billy Bob Thorton" is applicable, How would that IIF() be written.
Thanks

You can reverse the order of names for display purposes:

Mid([SetterCompany], InStr([SetterCompany], " ") + 1) & ", "
Left([SetterCompany], InStr([SetterCompany], " ") - 1)

will return Smith, Joe.

It will also return "Bob Thornton, Billy" and "von Beethoven, Ludwig". Names
with two blanks require a USB (Using Someone's Brain) interface since it might
be a multiword firstname, or lastname, or both.

Seriously... *correct your table structure*. It'll be some work but it will
pay dividends. for one thing, sorting by a calculated field will be very slow,
since it not only cannot use any indexes, but you must call two functions (or
four, if you use the suggestion above) for every single row.