Date sorts as text in union query using 'all' option. help please 1

[ulteriormotif]

While I still have some hair left, could someone give me a pointer on this one, please.

I have a simple union query used to add an 'all' option a list of dates in a combo box:

SELECT Date
FROM [DAT - Payments Total]
GROUP BY date
ORDER BY date

UNION SELECT "*"
FROM [DAT - Payments Total];

Works great in that respect, but the problem is that the dates then seem to be recognised as text and sorted as such, with the result that my date list looks something like:

*
10/03/2004
10/05/2004
12/03/2004
12/05/2004
14/04/2004
15/04/2004
16/04/2004
18/03/2004
19/03/2004

I really need the dates to sort as dates, not text. Any suggestions?

Fairly new to working in the SQL side of Access, so hopefully there's some simple qualifier or something that I just haven't come across yet.

 

[PHV]

Have you tried this ?
SELECT DISTINCT Format(myDate, "yyyy-mm-dd") As theDate
FROM [DAT - Payments Total]
ORDER BY Format(myDate, "yyyy-mm-dd")
UNION
SELECT DISTINCT "*"
FROM [DAT - Payments Total];
Or if you insist on the dd/mm/yyyy formating, you may consider adding a sort column hidden in the combo:
SELECT DISTINCT myDate As theDate, myDate As sortDate
FROM [DAT - Payments Total]
ORDER BY 2
UNION
SELECT DISTINCT "*", #01/01/1900#
FROM [DAT - Payments Total];

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ulteriormotif]

Brilliant, PVH! Thank you.

Here's what I ended up with:

SELECT DISTINCT format(Date, "yyyy-mm-dd") As sortDate, Date As theDate
FROM [DAT - Payments Total]
ORDER BY 1
UNION
SELECT DISTINCT #1900/01/01#, "*"
FROM [DAT - Payments Total];

For some reason I had to switch the sort column to the left to get it to work, but it does.