Adding 'New' to combobox, then sorting

[Karen]

If I use this code for a combobox rowsource, it works, I get the names of
only agents where the id is '3'

"Select AgentsID, fkAgencyID, AgentFullName from Agents WHERE fkAgencyID
= 3"

I want 'New' to be in the combobox list so I did this, which did add "(New)"
but all agents were selected, not just the agents

"Select AgentsID, fkAgencyID, AgentFullName from Agents UNION SELECT
0,0, "(New)" from Agents WHERE fkAgencyID = 3"

So I changed to this and it works

"Select 0,0,"(New)" from Agents UNION SELECT AgentsID, fkAgencyID,
AgentFullName from Agents WHERE fkAgencyID = 9

Now I want to sort but this does not work because I get zero results

"Select 0,0,"(New)" from Agents UNION SELECT AgentsID, fkAgencyID,
AgentFullName from Agents WHERE fkAgencyID = 9 ORDER BY AgentFullName"

I even tried this; no error but it isn't sorted

Select 0,0, "(New)" from Agents UNION ( Select AgentsID, fkAgencyID,
AgentFullName from Agents WHERE fkAgencyID = 9 Order by AgentFullName)

Any suggestions on how to add "(New)" to a combobox list, when the rowsource
is a SQL query, and sort the list with "(New)" on the top of the list?

 

[Steve Schapel]

Karen,

Try this...
SELECT AgentsID, fkAgencyID, AgentFullName FROM Agents
WHERE fkAgencyID = 9
UNION SELECT 0,0, '(New)' FROM Agents
ORDER BY AgentFullName

 

[Karen]

Steve,

That works! Thank you so much.

--
Karen

Karen,

Try this...
SELECT AgentsID, fkAgencyID, AgentFullName FROM Agents
WHERE fkAgencyID = 9
UNION SELECT 0,0, '(New)' FROM Agents
ORDER BY AgentFullName