union query, different WHERE, same table

[dontfwithchuck]

Hi, this is my first post.
I use Access XP.

I have a single table called Movies containing the following fields:
ID, Title, Genre, Rating, Location, Date

I want a datasheet with the Titles that have a date sorted in Descending order by date, then the Titles that don't have a date sorted by Title in Ascending order.

I have this:

SELECT [Title] & " " & "(" & [Rating] & ")" AS Expr1
FROM Movies
WHERE (((Movies.Date) Is Not Null) AND ((Movies.Genre)="Drama"))
ORDER BY Movies.Date DESC
UNION ALL
SELECT [Title] & " " & "(" & [Rating] & ")" AS Expr1
FROM Movies
WHERE (((Movies.Date) Is Null) AND (((Movies.Genre)="Drama"))
ORDER BY Movies.Date ASC;

I keep getting an error about the order by clause not containing fields that are in the select clause.

What am I doing wrong?
Thanks,
Chuck

[dontfwithchuck]

I've solved it!
The proper code should be:

SELECT Title, Rating, Date
FROM Movies
WHERE (((Movies.Genre)="Drama")) AND (((Movies.Date) Is Not Null))
UNION ALL SELECT Title, Rating, Date
From Movies
WHERE (((Movies.Genre)="Drama")) AND (((Movies.Date) Is Null))
ORDER BY Date DESC , Title;
 

It's just a simple matter to format my report now, and that is done.
Yea ME!

[NeoPa]

You've posted your question in the "insights" area.

I've moved it to the "questions" area so that it may get some interest.

[NeoPa]

I suspect you don't need anything quite so complicated.

Try this :

SELECT [Title],
       [Rating],
       [Date]
 
FROM Movies
 
WHERE [Genre]='Drama'
 
ODER BY [Date] DESC,
        [Title]