Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    34

    Unanswered: UNION query to create an "*Add New" item, how to ORDER BY

    Following instructions in my reference book that suggest that a UNION query is an easy to way to add micellaneous useful items to a combo box, I wrote the following sql:

    SELECT AgentID, FirstName & " " & LastName AS FullName
    FROM tblBrokerAgents
    UNION
    SELECT -1, "*Add a new agent" AS FullName
    FROM tblBrokerAgents;

    And I wrote some handy VB to pull up frmAddAgent when "*Add a new agent" is selected. (Sure I'm proud of myself! I didn't know any VB at all three weeks ago!).

    All well and good. But the persnicketty perfectionist in me wants to sort the Agents by LastName, FirstName. A friend suggested this:

    SELECT AgentID, FirstName & " " & LastName AS Fullname, FirstName, LastName
    FROM tblBrokerAgents
    UNION
    SELECT -1, "*Add a new agent" AS FullName, "", ""
    FROM tblBrokerAgents
    ORDER BY LastName, FirstName

    That does sort the names correctly, but it also adds two blank rows to my combo box.

    Is there a better way I can achieve this?

    Thanks in advance!

  2. #2
    Join Date
    Oct 2003
    Posts
    66
    Okay I tried this code and it doesn't add blanks in my combo box. I even set up a test database using same names and still works without adding blanks. Check you table to make sure you don't have blank entries in the table or osmehitng...otherwise i am baffled.

  3. #3
    Join Date
    Oct 2003
    Posts
    34
    I am an idiot. You're exactly right; there were two blank entries in the table I forgot to delete, and they were showing up at the top when I sorted, and at the bottom when I didn't sort (so I didn't notice them then).

    ACK! Thank you.

Posting Permissions

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