Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

ulteriormotif

Technical User
May 18, 2003
79
NZ
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.
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top