Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Carol W.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Carol W.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •