Noodlescratcher of a ComboBox Qry (sort items by date) 1

[newyorkny]

Hello, Tippers!

I have a combo-box that drops down available reports. The name of each report is in the following format:

Transfer[MON]A200X

Example:

TransferFebA2004

The problem is that the combo-box list is not in chrono order.

What I would like to do is order by month and year. Say, ok, Access, look first at the 17th character (the last digit of the year) and order by it in descending order, THEN look at the 8,9,10 spots and order according to month.

Now THATSA difficult query! Can anyone help with this, as Ned Flanders might say "dilly of a pickle?"

Thanks in advance for your time and consideration!

NY

 

[Crowley16]

erm, you might want to try to extract the year and month and put them into fields first, then just order by those 2 fields, and keep them hidden on the actual report...

something along the lines of:

SELECT blah, blah, ..., MID(9, 3) AS Month, Right(4) AS Year FROM ...

--------------------
Procrastinate Now!

 

[PHV]

Something like this ?
TransferFebA2004
ORDER BY CDate("01-" & Mid(ReportName,9,3) & "-" & Right(ReportName,4))

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

 

[newyorkny]

Thanks, PHV.

That's awesome work.