Ordering Date in Union Query (1 Viewer)

[Mario]

Hello,

I have a very simple union query (Query1) that combined dates from 2 tables (Tbl1 and Tbl2). A combobox show the result of this query. However, I can't have the union query to keep:

1) the descending order asked
2) the "Medium Date" format of the original data


Here is the result of individual queries on Tbl1 and Tbl2

   Payment Date Submitted (Tbl1)
   02-May-03
   01-May-03
   30-Apr-03
   29-Apr-03
   28-Apr-03

  Payment Date Submitted (Tbl2)
  01-May-03
  30-Apr-03
  28-Apr-03
  25-Apr-03
  21-Apr-03
  01-May-02

And this is the results from the union query (Query1)

  Payment Date Submitted
  05/01/02
  04/21/03
  04/25/03
  04/28/03
  04/29/03
  04/30/03
  05/01/03
  05/02/03

Here is the SQL of my union query:

SELECT [Tbl1].[Payment Date Submitted]
FROM [Tbl1]
GROUP BY [Tbl1].[Payment Date Submitted]
HAVING ((([Tbl1].[Payment Date Submitted]) Is Not Null))
ORDER BY [Tbl1].[Payment Date Submitted] DESC
UNION SELECT [Tbl2].[Payment Date Submitted]
FROM [Tbl2]
GROUP BY [Tbl2].[Payment Date Submitted];

Is there a way that I can have the results of Query1 in DECENDING order as I asked for in My SQL statement?

 

[Pat Hartman]

I believe the recordset will be sorted by the order by clause of the last select.

SELECT [Tbl1].[Payment Date Submitted]
FROM [Tbl1]
Where [Tbl1].[Payment Date Submitted] Is Not Null
UNION SELECT [Tbl2].[Payment Date Submitted]
FROM [Tbl2]
Order BY [Tbl2].[Payment Date Submitted] DESC;

 

[Mario]

Thanks for your help Pat, it allowed me to solve my problem. However I must correct one little thing in your solution

SELECT [Tbl1].[Payment Date Submitted]
FROM [Tbl1]
WHERE ((([Tbl1].[Payment Date Submitted]) Is Not Null))
UNION SELECT [Tbl2].[Payment Date Submitted]
FROM [Tbl2]
ORDER BY [B][Tbl1][/B] .[Payment Date Submitted] DESC;

Access will not allow an "ORDER BY" on field outside of the first query. From Access:

The ORDER BY expression ([Tbl2].[Payment Date Submitted]) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.

But the location of the ORDER BY clause in my SQL statement made the difference.

I wouldn't have been able to find this without your help. Many thanks.