Sort By Date in VB6!

[vbsixer]

Howdy Folks...

So, I have a calendar program I have written which basically stores a number of entries on any given date, as set by teh date/time picker component.

Basically, this is all written to an access db I use for my program output and re-read later.

The idea is:

1) A DAO query is set up to open the db and read in all the DATES to a list box
2) User clicks on any given date, as shown in the listbox
3) By the use of hte ID, it shows any events for the date, as per whats stored in the database.

All I Want to do, is sort that list of dates in my listbox (Which comes from the database) by DATE i.e if I had say:

19/01/2008
25/02/2008
30/01/2009
..it would go in that order as its 3 dates oldest to newest....

As things stand just now, I get:

19/01/2008
30/01/2009
25/02/2008

...can someone tell me how I can get it to sort on DATE, based on oldest to newest???

Cheers

[Merri]

If you use the international date format of YYYY-MM-DD your problem is solved automatically. In any other case you'd have to convert the strings to date datatype, sort them and then add to listbox in the correct order, because listbox does not support sorting by dates.

[vbsixer]

How would I go about altering this then, as I use the Date Time picker to add the dates to the database and just read the values back....

ALso, thanks for your help...I can live with an alteration to hte format if it sorts properly!!

Cheers...

[Merri]

The "Just Works" solution:

List1.AddItem Format$(CDate(strDateFromDatabase), "YYYY-MM-DD")

However, if the date is saved in the database as a date (ie. the format of the field is date), then you may even directly query in the YYYY-MM-DD format from the database, which would be a better solution.

[vbsixer]

Cheers Merri

This does indeed alter the format and I have this plugged in as is...

The problem is, It still doesnt sort automatically and is only sorting on the DAY of the week i.e. from 1st to 31st is in order but in my scenaro above, the year is still out i.e. still works as per:

2008-01-01
2007-02-02
2008-03-03

where 2007 should be first.....!

Thanks for your help...

[Merri]

You can set the listbox Sorted property to True.

Alternatively, I missed this thought pattern earlier: if the dates are stored as real dates in the database, then you can simply let the call to the database do the sorting for you, then you can use any date format you like as long as the listbox is not automatically sorted.

[realbogus]

to start with, use ADO. Then set the data type as a date.

The rest will take care of itself. Otherwise, the system views the data as a string and confusion could abound.

[vbsixer]

You can set the listbox Sorted property to True.

Alternatively, I missed this thought pattern earlier: if the dates are stored as real dates in the database, then you can simply let the call to the database do the sorting for you, then you can use any date format you like as long as the listbox is not automatically sorted.

This works fine buddy - thanks VERY much.

Alas, I now have the earliest dates first and the latest last !! Ohh happy days

Thank again...appreciate your help with this.

[si_the_geek]

Assuming you've used an Order By clause, just add the Desc keyword, eg:

...
ORDER BY DateField DESC

[vbsixer]

Cheers Si....all looking good now thanks to yer all for the help.

Ta!!!