Sorting

[Stephanie]

Hello.
I have a form with a record source: SELECT Contacts.* FROM Contacts;

Contacts has fields LastName, FirstName and CompanyName.
Not every Contact with a LastName has a CompanyName, and not every
CompanyName has a LastName.

I'd like the form sort order to be: if there is a LastName sort by it, then
by FirstName. If there isn't a LastName sort by CompanyName.

So I'll have:
Jones, Bob (no company)
Jones, Susie (Johnson & Johnson)
Zimmer, Skip (no company)
(no last name) Sysco
(no last name) Tyson

Every sort I've tried to do ends up with false information in one of the
fields, such as LastName = Name?

I'd appreciate your query assistance! Thanks.

 

[Jerry Whittle]

SELECT Contacts.*
FROM Contacts
ORDER BY NZ([LastName],"zzz"),
Contacts.FirstName,
Contacts.CompanyName ;

 

[Stephanie]

Oh, sure! Make it look so easy! Thanks for the help- I appreciate you taking
the time to teach me a new trick!

SELECT Contacts.*
FROM Contacts
ORDER BY NZ([LastName],"zzz"),
Contacts.FirstName,
Contacts.CompanyName ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello.
I have a form with a record source: SELECT Contacts.* FROM Contacts;

Contacts has fields LastName, FirstName and CompanyName.
Not every Contact with a LastName has a CompanyName, and not every
CompanyName has a LastName.

I'd like the form sort order to be: if there is a LastName sort by it, then
by FirstName. If there isn't a LastName sort by CompanyName.

So I'll have:
Jones, Bob (no company)
Jones, Susie (Johnson & Johnson)
Zimmer, Skip (no company)
(no last name) Sysco
(no last name) Tyson

Every sort I've tried to do ends up with false information in one of the
fields, such as LastName = Name?

I'd appreciate your query assistance! Thanks.