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
SELECT -1, "*Add a new agent" AS FullName
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
SELECT -1, "*Add a new agent" AS FullName, "", ""
ORDER BY LastName, FirstName
That does sort the names correctly, but it also adds two blank rows to my combo box.
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.
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).