Sort order and seconday sort order

[Guest]

Hi

I hope that someone can help?

I have a table with two fields "Days since last contact" and diary date.

I want to have a certain sort order whereby the records are sorted by diary
date ascending if the diary date is < date()+1 and then by days since last
contacted descending.

However if the diary date is > date()+1 then I just want to sort by days
since last contact Descending.

The following is how I would like the data to appear

DaysLastcontact DiaryDate
30.00 04/04/2007
7.00 04/04/2007
2.00 04/04/2007
30.00 05/04/2007
7.00 05/04/2007
2.00 05/04/2007
1.00 05/04/2007
99.00 30/09/2007 The change in sort order starts here and the diary date
97.00 26/05/2007 does not matter
30.00 07/04/2007
30.00 30/04/2007
30.00 06/04/2007
30.00 06/04/2007
30.00 06/04/2007
7.00 06/04/2007
3.00 06/04/2007
2.00 06/04/2007
1.00 07/04/2007

I think that the query may be set up using an iif statement but Iam not sure
of the correct syntax or if there is another solution

Any suggestions would be much appreciated

kind regards

Colin

 

[Guest]

SELECT ColinDates.DaysLastcontact,
ColinDates.DiaryDate,
1 as SortOrder1,
ColinDates.DiaryDate as SortOrder2
FROM ColinDates
WHERE ColinDates.DiaryDate < Date()+1
UNION
SELECT ColinDates.DaysLastcontact,
ColinDates.DiaryDate,
2 as SortOrder1,
#1/1/1950# as SortOrder2
FROM ColinDates
WHERE ColinDates.DiaryDate > Date()+1
ORDER BY SortOrder1,
SortOrder2,
ColinDates.DaysLastcontact DESC;

Make sure that you have the proper table and field names. Also your
dd/mm/yyyy date format could cause problems.