Insert “All” before results of SQL

Topic

New Reply

I have a dropdown list on a form whose rowsource is the following SQL statement: SELECT [Org Table].[Org Organization] FROM [Org Table] ORDER BY [Org Organization];

I would like to insert the entry “All” before the first entry resulting from the execution of the SQL statement (even if “All” is out of order). I’m not sure of the syntax required to do this.

Thanks, in advance, for any assistance.

Reply | Quote

Replies

SELECT [Org Table].[Org Organization] FROM [Org Table]
Union Select “All” as [Org Organization] FROM [Org Table]
ORDER BY [Org Organization]

The results will be sorted alphabetically so “All” should be near the top.
If not you could try this

SELECT [Org Table].[Org Organization] FROM [Org Table]
Union Select ” All” as [Org Organization] FROM [Org Table]
ORDER BY [Org Organization]

By putting a leading space in ” All” it should come first.

Of course you then to deal with the selection of “All” in what you do next.

Reply | Quote

One option is to use an SQL statement like this:

SELECT [Org Table].[Org Organization], 2 AS GroupSort, [Org Table].[Org Organization] AS NameSort
FROM [Org Table]
UNION SELECT “ALL”, 1, “ALL”
FROM [Org Table]
ORDER BY GroupSort, NameSort

If you’re sure that no Org Organization begins with a non-alphabetic character, you could use the shorter

SELECT [Org Table].[Org Organization]
FROM [Org Table]
UNION SELECT “(ALL)”
FROM [Org Table]

Here, the opening parenthesis ( ensures that (ALL) will be sorted before all alphabetic entries.

Reply | Quote

I can’t guarantee that the org names won’t begin with a non-alphabetic character (although it’s unlikely), so I used Hans’ first option. Worked great!

I have VBA code behind the form that calls one function if the user selects “All” and another function if the user selects any of the other choices.

Thanks to you both. I know I can always count on Woody’s Lounge

Reply | Quote