Conditional Order By 1

[djugene188]

I've searched the forums for a solution to this, but couldn't find anything quite for my situation. I have a client table that I need to sort in two different ways based on if the company field is null or not. If the company field is null then the rows need to sort by Last Name, First Name. If there is a company name, then the rows need to sort by Company, Address, Last Name, First Name.

EX:
COMPANY ADDRESS FNAME LNAME
Jane Doe
200 W. Washington John Doe
15 West State St. Mark Smith
ABC Company 400 North Jackson Alice Swimmer
ABC Company 1015 South Maple Eric Carroll
DEF Company 125 East Adams Mike Johns
DEF Company 125 East Adams Erica Walker
DEF Company 300 West Canal St. Maria Almond

I've tried Inner Joins, combined queries, IIF statements in the ORDER BY but none have gotten me the desired results.
I've gotten close using the IIF Statement with column numbers, but I can't get the right syntax to have more than one column for sorting

--- ORDER BY IIF (Company Is Null, 4, 1) ---

Any information that would point me in the right direction is much appreciated.

Please let me know if any other information is needed.

 

[PHV]

What about this ?
ORDER BY Nz(Company + Address, ' '), [Last Name], [First Name]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[djugene188]

That did it. Thank You! A star for you.

 

[djugene188]

Thank you so much for your help!
I did not think about using NZ...