Sorting Combobox?

[Fionn]

Hi,
I have a combobox which displays a list of all the months and an "All Months" selection. I have done this using this sql statement.
SELECT MonthName AS Filter, MonthName FROM qryMonths UNION SELECT '*' AS Filter, "All Records" AS MonthName From qryMonths;

My problem is this query is being sorted alphabetically (by month name) despite me having it sorted by MonthID in qryMonths. How can I change the sorting of this combobox? Is there no manual way of just choosing? Can it be done in VBA? All I can find on the web is http://office.microsoft.com/en-ie/ac...005187786.aspx .
Which doesn't work.
Any suggestions?
Thanks.

[SoftwareMatters]

Add the ID field to your query and then sort by it something like this:

SELECT MonthName AS Filter, MonthName, MonthID FROM qryMonths UNION SELECT '*' AS Filter, "All Records" AS MonthName, MonthID From qryMonths ORDER BY [MonthID]

[Fionn]

Add the ID field to your query and then sort by it something like this:

SELECT MonthName AS Filter, MonthName, MonthID FROM qryMonths UNION SELECT '*' AS Filter, "All Records" AS MonthName, MonthID From qryMonths ORDER BY [MonthID]

Thanks but if I do that it displays an "All records" selection for each record rather than just one.
I tried SELECT DISTINCT and DISTINCTROW but neither had any effect. Any suggestions?

[SoftwareMatters]

Do you have grouping switched on in the qryMonths query? If so this doesn't quite make sense if you want to order by ID.

[Fionn]

Nope don't have grouping switched on. It's as simple as

SELECT tblMonths.MonthName, tblMonths.MonthIDFROM tblMonths;

[SoftwareMatters]

I think I see what's going on now - try this:

SELECT MonthName AS Filter, MonthName, MonthID FROM qryMonths UNION SELECT '*' AS Filter, "All Records" AS MonthName, 1 as MonthID From qryMonths ORDER BY [MonthID]

NB: just change the number 1 to whatever you need depending on if you want the 'All Records' option to be at the top or the bottom.

[Fionn]

I think I see what's going on now - try this:

SELECT MonthName AS Filter, MonthName, MonthID FROM qryMonths UNION SELECT '*' AS Filter, "All Records" AS MonthName, 1 as MonthID From qryMonths ORDER BY [MonthID]

NB: just change the number 1 to whatever you need depending on if you want the 'All Records' option to be at the top or the bottom.

Thanks works grand.