Help with a UNION select Please

[pmcleod]

I have a query that requires a selection from my list box "[Forms]![ViewReports]![ListFilterName]"

I have a list box with a UNION so that I can query all contacts. The bound column is one.

SELECT 0,"<<ALL","TEAM MEMBERS>>" from Contacts UNION (SELECT Contacts.ID, Contacts.[First Name], Contacts.[Last Name] FROM Contacts ORDER BY [Last Name]);

Problems

The list is not ordering by last name.

The query does not assume all contacts when "<<ALL","TEAM MEMBERS>>" is selected.

Where did I go wrong? What's missing?

 

[sjohnson77]

I think I may know...

When sorting UNION queries, I believe that you have to ORDER BY the column number, rather than column name. If both column names do not match, it confuses the system.

SELECT 0,"<<ALL","TEAM MEMBERS>>" from Contacts UNION (SELECT Contacts.ID, Contacts.[First Name], Contacts.[Last Name] FROM Contacts ORDER BY 3);

If that doesn't work, try ORDER BY 2. I can't remember if the column number starts at 1 or 0.

Hope this helps.

Thanks,

Steve Johnson

 

[pmcleod]

I neglected to add IIf([Forms]![ViewReports]![ListFilterName]=0,[Issues.AssignedTo],[Forms]![ViewReports]![ListFilterName]) to my query. Silly me ....

Ok so I tried to ORDER by number and that did not seem to work.