Need help with combo box

[mel]

I have a table with a list of institutions in the US and abroad. I have
a list of Countries displayed on a combo box on my form using SELECT
DISTINCT... so the Countries are displayed in alpha order. My user
wants the option "United States" displayed first in the list, followed
by the rest of the Countries in alpha order.

I have tried using GROUP BY and I'm able to get "United States" at the
top of the list, but I can't get the rest of the Countries to display
in alpha order. Can anyone help? Below is my SQL code. Thanks.

SELECT First(tblCollegeList.ID) AS FirstOfID, tblCollegeList.Country
FROM tblCollegeList
GROUP BY tblCollegeList.Country
ORDER BY First(tblCollegeList.ID), tblCollegeList.Country;

 

[Allen Browne]

Try:

SELECT First(tblCollegeList.ID) AS FirstOfID, tblCollegeList.Country
FROM tblCollegeList
GROUP BY tblCollegeList.Country
ORDER BY (tblCollegeList.Country = "United States"),
First(tblCollegeList.ID), tblCollegeList.Country;

The first field in the ORDER BY clause will be True for only one country,
and True sorts before False.

 

[mel]

That worked perfect! I knew I was missing something in the ORDER BY
clause. Thanks so much.