Query sorting challenge; how to get a mix of ascending & descending.

[StargateFanNotAtHome]

I have a list of files/folders that sorts in a normal style, in
alphabetical order, in the style of these example folders here:

ABCs of Filing
Annual Report - 2006
Annual Report - 2007
Annual Report - 2008
Annual Report - 2009
Business Continuity Plan
Debits and Credits
Summary of Corporate Plan - 2006
Summary of Corporate Plan - 2007
ORG - 2007
ORG - 2009
Website Edits
Website Server Change

The trouble is that I must have the same ascending alphabetical order
in the folder names themselves, as seen above, but the chronological
order of the groups of files must be descending, so that I get this:

ABCs of Filing
Annual Report - 2009
Annual Report - 2008
Annual Report - 2007
Annual Report - 2006
Business Continuity Plan
Debits and Credits
Summary of Corporate Plan - 2007
Summary of Corporate Plan - 2006
ORG - 2009
ORG - 2007
Website Edits
Website Server Change

Can it be done? I've tried several things but I'm not getting the
needed results and I'm not sure how to approach modifications to the
query.

Thanks so much for any help!! D

 

[John Spencer]

If the last four characters are numbers then you want to sort in descending
within the group.

SortFieldA: IIF(IsNumeric(Right(ItemName,4)),LEFT(ItemName,
Len(ItemName)-4),ItemName)

SortFieldB:IIF(IsNumeric(Right(ItemName,4)),Right(ItemName,4),Null)

So the Order By clause would look like

ORDER BY IIF(IsNumeric(Right(ItemName,4)),LEFT(ItemName,
Len(ItemName)-4),ItemName),
IIF(IsNumeric(Right(ItemName,4)),Right(ItemName,4),Null) Desc

Another method that might work

ORDER BY IIF(IsNumeric(Right(ItemName,4)),LEFT(ItemName, Len(ItemName)-4) &
9999 - Val(Right(ItemName,4)),ItemName)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County