Sorting a listbox via chronological order

Please bear with me, but here I go again :slight_smile:

I have a 3 column list box which pretty much replicates the same information you would get in an “open file” system dialog box.

I don’t want to use the “open dialog” because this is just a simple “fly-out” box that shows the user, as a “reminder” to what has been done before and also gives them the opportunity to apply the same filename in a test series without having to remember it:

If you look at the middle column (> Test Number, Date etc…) this “almost” does what I want because the file name includes a “Test Number” + an incremental numeric number, then date, time etc.

For the most part this works fine because listboxes sort alphabetically, as can be seen with the “Test Number” column. There is no doubt about the chronological order - and this works regardless of the system date/time settings.


The test number is only applied to files that are recorded within the system. Imported files are not given a test number or date (as per the last 3 items in the list) as the details are input by the user. Therefore the sorting can become confusing. They can sort via Test “Name” but that’s still not good enough - they need to know what was done recently.

This was tested by my “Alpha” tester, and he became confused of how to find the most recent test.

So, now I have a third column “Chronological” and of course we can see that it doesn’t sort correctly because a listbox will only sort alphabetically. I’m happy enough to just have a number in there, ie. using TotalSeconds, but I couldn’t even get that working.

For what it’s worth, here’s the code:

[code] Dim myFolder As FolderItem = SpecialFolder.ApplicationData.Child(myAppDataFilesFolder)
Dim delimiter As String = &u22b3
Dim delimiterPos as Integer
Dim testName, testNumber As String

listSavedFiles.DeleteAllRows // first clear the previous list

Dim filecount As Integer = myFolder.Count

For i As Integer = 1 To filecount
  Dim f As FolderItem = myFolder.Item(i)
  If f <> Nil AND f.Name.Right(6) = ".final" Then //Only show *.final files in the listbox
    //-------------------------------------------------------POPULATE THE LISTBOX-------------
    delimiterPos = InStr(f.Name, delimiter)
    testName = Left(f.Name, delimiterPos-2)                //extract test Name from filename
    testNumber = Right(f.Name, Len(f.Name)-delimiterPos+1) //extract test Number from filename
    listSavedFiles.Cell(ListSavedFiles.LastIndex,1) = testNumber
    Dim sysDateMod As String = (f.ModificationDate.ShortDate)
    'Dim d As New Date
    'Dim sysDateMod As Double = (d.TotalSeconds)/60
    'sysDateMod = Format(sysDateMod,"0000000000")
    listSavedFiles.Cell(ListSavedFiles.LastIndex,2) = sysDateMod
  End If

You can see near the end where I experimented with “TotalSeconds” with the variable sysDateMod, in the remarked out code. I had too many errors.

I’m hoping there is a more elegant solution and appreciate your thoughts.

Just use the SQLDate(Time) Format in the Chronological Column. :wink:

Thanks Sascha, does that mean I have to create an SQL database?

Look at the properties of the date object (which you should have done before you wrote your code)

Well, I can’t believe it appears that I’ve got it:

It now Looks the same order as the standard “open File” dialog.

This is the code I used:

Dim sysDateMod As New Date
sysDateMod.SQLDateTime = Str(f.ModificationDate)
listSavedFiles.Cell(ListSavedFiles.LastIndex,2) = Str(sysDateMod)

I have to be honest and say I don’t understand why I had to convert to string in 2 places - perhaps it’s wrong in some other way I can’t see, but it does seem to have worked.

Or (more complex, but if you have time): put the sqlDate in Tag and sort using that.

Remember SQLDate = ISO 8601.

Wrong sort order ! Another click is needed.

Usually, the first click is enough.

[quote=385479:@Emile Schwarz]Wrong sort order ! Another click is needed.

Usually, the first click is enough.[/quote]

Thanks Emile, But this is the preferred default order I want - most recent first. Or have I missed something?

[EDIT] My aplologies - the first graphic showed the order the other way but I do want most recent first.

listSavedFiles.Cell(ListSavedFiles.LastIndex,2) = f.modificationdate.SQLDateTime

If this is OK for you, it is for me :wink:

[quote=385476:@Steve Kelepouris]

Dim sysDateMod As New Date
sysDateMod.SQLDateTime = Str(f.ModificationDate)

I have to be honest and say I don’t understand why I had to convert to string in 2 places - perhaps it’s wrong in some other way I can’t see, but it does seem to have worked.[/quote]

Because SQLDateTime is a string, ModificationDate is a date object.

Which you would know if you would bother to read the documentation.


More importantly, and the nuance that the OP obviously didn’t grok, is that SQLDateTime is a string property of any Date object. So no “conversion” of any kind, nor the instantiation of a new Date object to which the file’s modification date is assigned, is necessary. You can simply access f.ModificationDate’s SQLDateTime property directly as I pointed out earlier.

LOL… wonder how many people get the reference to “A Stranger in a Strange Land”… Micheal Valentine

Only the best people :slight_smile:

:frowning: … I’m sad now …


Markus… “Stranger in a Strange Land”… Robert Heinlein… one of the best SciFi books in history (2nd only to Foundation by Issac Asimov)… if you have never read it, and are a fan of great SciFi… you must read it… “Micheal Valentine” is the name of the main character…

Actually, I thought “grok” had entered the mainstream lexicon a long time ago, and in fact it appears to now be in the Merriam-Webster dictionary:

My statement was not exclusive, Markus - you can still be one of the Best People and not know grok! :slight_smile:

Thank you Julia from the bottom of my grok-less heart :slight_smile: for posting that so succinctly. Also a big thanks to Sasha and Emile for the positive solutions and comments.

@Markus, I do try to read the documentation, and I did spend an hour or so trying to find a solution, but how would I find the solution if I didn’t know exactly what it was I was looking for in the first place? Think about it.

To me, that is a known unknown, ie. I knew I didn’t know, but also knew that there was a simple solution that I didn’t know about, and my preference was to ask on this forum than stumbling across it (and getting it wrong) whilst wading through reams of documentation.

The problem with negative and nonconstructive comments is that it can lead to someone (like me) to read some part of the documentation, implement it, seemingly working ok, but oblivious to potential issues the code may cause down the track. All because they (me) could not be bothered with putting up with negative comments posting on this forum. Those comments become much more “sharp” if you are an amateur :slight_smile:

My view is that the best way of learning is from other people - not just through reading the documentation.


So again, after reading as much documentation that I can handle, I cannot work this part out.

What I need to do now, to finish this off, is to have the listbox (on opening) select the 3rd column and sort it in descending order.

The code that I’ve blundered across/upon is:

    listSavedFiles.ColumnSortDirection(2) = Listbox.SortDescending //newest to oldest
    listSavedFiles.SortedColumn = 2

It doesn’t seem right, but it does work as in sorting the chronological column in the way I expect/want, but the only thing missing is that it doesn’t “highlight” the column heading.

I don’t wish to be “spoon-fed” and happy to read the docs if I can be pointed to the part that explains how to do this.

@Dave: Love SciFi and Asimov. You’ve made me realise something. Well, you and Julia, with the grok comment - I’ll have to buy that book “A Stranger in a Strange Land” and the result of the reading will, quite possibly be far more interesting than this thread could ever provide :slight_smile:


Instead of

listSavedFiles.SortedColumn = 2 listSavedFiles.Sort

you can try


This won’t leave the heading highlighted, but it will leave the sort arrow visible in the heading. I don’t know that there’s a simple way to leave the heading highlighted, I think it’s a momentary button kind of thing.