[AccessD] Sorting combo based on Union query

Hi all

Well, my smart idea doesn't work.
However, you can add the field to sort on without giving it a name
whatsoever:

  SELECT 
    Field, 1
  FROM 
    tblSomeTable
  UNION
  SELECT 
    'Select all', 0
  FROM
    tblSomeTable
  ORDER BY 2, 1 ASC;

Note the appended field to sort on (which does not need to be included
in the column count of the combo- or listbox) and the use of 1 and 2 as
field alias. This means you don't need to create new field names and the
field name you wish to display is picked once from the original
query/table (field).

/gustav

>>> Gustav at cactus.dk 15-02-2005 16:22:53 >>>
Hi Neal

You can add

.. ORDER BY ABS([field]='All') DESC, [field] ASC;

/gustav

>>> nkling at co.montgomery.ny.us 15-02-2005 16:06:55 >>>
I'm adding an "All" selection to a combo box using a union query more
or less like this:

SELECT "All" as snoot from MyTable UNION SELECT MyTable.Field FROM
MyTable;

MyTable.Field is defined as Text but contains Integer values.

Naturally the ascending sort order puts the numbers first. The "All"
comes at the end of the list, and I want it at the top. I have tried
adding an ORDER BY clause and by using DESC the "All" is at the top,
but
of course the numbers are then in reverse order. I have also swapped
the
order of the select statements but that makes no difference.

So, does anyone have a suggestion as to how I can get "All" at the top
of the list with the rest of the list in numeric order?

Thanks,
Neal