[AccessD] Sorting combo based on Union query

Neal,

I got this from Database Creations' products.

Make your query like this:

SELECT "<All>" AS DisplayValue, "*" AS KeyValue
FROM MyTable
UNION
SELECT DISTINCT MyTable.Field AS DisplayValue, MyTable.Field AS KeyValue
FROM MyTable
ORDER BY KeyValue;

That is, make two identical fields from MyTable.Field and use the
KeyValue as the Sorting field. Make the width of the 2nd field 0 in
your combo box and use it as the Bound field. Thus when <All> is
selected, the bound value will be * which can be inserted into a SQL
string to make 'LIKE *'

Steve Erbach
Neenah, Wi


Instead of using "All" you could try "<All>" 


On Tue, 15 Feb 2005 10:06:55 -0500, Neal Kling
<nkling at co.montgomery.ny.us> wrote:
> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com