SQL: Problems combing UNION SELECT & ORDER BY (with logic)

[ohmydatabase]

Hello,

I am running into issues combing the UNION SELECT and ORDER BY commands in Access SQL. My two select statements select from the same table and both utilize SELECT *. The WHERE conditions on each of these SELECT's are slightly different and call for two separate queries. I then use one of the fields from the SELECT statement to customize the ordering of the query.

SELECT * 
 FROM [tblStatus] 
  WHERE Program = 'ABC' 
  AND (StartDate BETWEEN #8/29/2017# AND #8/31/2017# OR Ongoing = True) 
  AND 
   (
    OGAuthor IN 
     (
      SELECT LeadName 
       FROM tblLead 
        WHERE tblLead.Program = 'ABC' 
        And Department = 'SW'
     ) 
    OR OGAuthor IN 
     (
      SELECT FullName 
       FROM tblUsers 
        WHERE tblUsers.Department = 'SW'
     )
   ) 
  AND Urgent = True 
  AND isCurrent = True 
UNION SELECT * 
 FROM [tblStatus] 
  WHERE OGAuthor = 'John Smith' 
  AND (StartDate BETWEEN #8/29/2017# AND #8/31/2017# OR Ongoing = True) 
  AND Urgent = True 
  AND isCurrent = True 
ORDER BY IIf([Program] Like '*General', 1, 0) DESC, Program, SubProgram, Task, Initials DESC, StartDate DESC, OGStartDate DESC, ID ASC;

I believe the problem is coming from the bit of logic in my ORDER BY clause. If I remove the "IIf" logic from the ORDER BY statement, it works. However, I really want to use that bit because it's better (to me) to use that instead of introducing an entirely new field just for ordering based on the likeliness to "*General" Currently, I get the following error.

Runtime Error: 3351
The ORDER BY expression (IIf([Program] Like '*General',1,0))) includes fields that are not selected by the query. Only those fields requested in the first query can be included in a ORDER BY expression.

This is strange to me because 'Program' is included in both queries. So this error is messing me up.



Additional Articles:
https://www.pcreview.co.uk/threads/u...-work.1165529/
https://www.accessforums.net/showthr...723#post368723

[PLangley]

You need to create a column in your both queries whose value is
(IIf([Program] Like '*General', 1, 0)) As SortP

Then use the SortP to order by.

Example (from one of my databases):

SELECT Equipment.Equipment, (IIf([Equipment] Like "*Line*",1,0)) AS SortP
FROM Equipment
UNION ALL SELECT Equipment.Equipment, (IIf([Equipment] Like "*Cup*",2,0)) AS SortP
FROM Equipment
ORDER BY SortP DESC;

Regards,
Pat Langley

[ohmydatabase]

I was having issues with that previously. What I was noticing was that the 'Like' function in VBA didn't go well when defining a new Field SortP. This could have something to do with the use of SELECT *, but I tried something similar and it didn't see to like it. But I'll give this method another go. Thank you.

[PLangley]

Be sure to enclose the entire formula (IIF..) in parenthesis when setting up the column, as I did in the example. Watch the use of single-quotes vs double-quotes. In VBA, use double-quotes except when putting quotes around strings inside of strings. Example: strSQL = "SELECT * FROM MyTable WHERE x = 'my value' ORDER BY x". Notice the use of the single quotes around the string value.

Hope that helps.
Pat Langley

(sheeee, I can't seem to say this right today. edited 3 times)

[ohmydatabase]

Thanks for the help!

I got around this problem by getting rid of the UNION SELECT clause. Im happy it worked, but a little let down as to why I couldn't get the IIf logic to work. Another oddity I noticed about the UNION SELECT function is that it will only query strings to 255 characters. This problem was also avoided by getting rid of the UNION SELECT clause.