sql statement with if clause?

  • Thread starter Thread starter Dan Nash
  • Start date Start date
D

Dan Nash

hi guys

I have a contacts table, with firstname, surname and company.

In showing this list, I want to order the data so that its ordered by
surname, firstname, company.

So...

"SELECT * FROM Contacts ORDER BY SurName, FirstName, Company"

This wors fine, however some records dont have a firstname or surname, and I
want these records to appear alphabetically in the list, with the company
name. Is there any way in sql of saying ORDER BY Surname Else if Surname =
"" ORDER BY Company?

The only way I can think of doing it is to copy the Company field into the
SurName field if it's blank when created. However, this seems fairly long
winded. Is there any easier way?

Any ideas much appreciated


Dan
 
Dan

When you say you want the Company name to appear "alphabetically in the
list", you'll need to be a bit more specific. The SQL you posted will cause
the Company to appear "alphabetically". If you have multiple Surname and
FirstName fields empty, the Company names will be sorted alphabetically
(with blank Surname and FirstName), then by the filled Surname/FirstName.

Are you saying you want the Company name to be sorted alphabetically, as the
primary sort field, then the Surname/Firstname fields? If so, just change
the order of your ORDER BY clause.

Good luck

Jeff Boyce
<Access MVP>
 
I just played a little with this and it appeared to work.

SELECT * FROM Contacts ORDER BY Nz(SurName, Company), FirstName, Company
 
Hi,


Sure.


ORDER BY Nz(SurName, Company), FirstName, Company


The order by clause accepts expressions. It is generally suggested to avoid
ordering by on non-selected expressions, since it may confuse the reader,
but otherwise, you can order by on expressions even if the expression is not
in the SELECT clause.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top