Sort ListBox contents by date in RealBasic

Many applications written using RealBasic use the ListBox control because it is a very useful control for storing lists of items. A simple ListBox contains just one list of items, such as Sunday, Monday, Tuesday, or Apple, Oranges, Pears and so on. However, it is possible for a ListBox control to have multiple columns and it then looks similar to a spreadsheet or a database table. There can be hundreds or even thousands of rows and up to 64 columns, which makes it ideal for storing data.

No matter what type of data you put into a ListBox control, it is always stored as plain text. So Bob Smith is stored as the string "Bob Smith", the number 27 is stored as the string "27" and the date 12/08/08 is stored as the plain text string "12/08/08". The ListBox control has no concept of numbers or dates, and everything is treated as a text string. When you want to store a number or a date in a ListBox you must therefore convert it to a string first, for example, str(27), str(mynum) or mydate.ShortDate

You will frequently want to sort the data in a ListBox and this is a piece of cake if the contents are text strings because ListBoxes can perform an alphabetical sort for you on any column you specify. For example, if you store people's names in a ListBox you can set the ListBox's SortedColumn to the one containing the last name, set the ColumnSortDirection to -1 (descending) or 1 (ascending) and then use the Sort method like this:

ListBox1.SortedColumn=0
ListBox1.ColumnSortDirection(0)=1
ListBox1.Sort

If the ListBox contains either numbers or dates, this code just won't work because the Sort method treats the contents as plain text. This means it will try to sort the numbers 57, 301, and 64 as text and since the Ascii character 3 comes before Ascii 5, sorting will result in 301 being placed first, 57 second and 64 last. Similarly, dates cannot be sorted because "Friday, 20 July 2007" would be placed before "Sunday, 24 December 2000" because F comes before S in the alphabet.

So how do we sort dates and numbers? The technique used for dates and numbers is actually almost identical, so I'll show how to sort a ListBox containing dates because you'll also see how to sort numbers at the same time.

Sorting dates
We saw that if you try to sort the numbers 57, 301, and 64 it comes out wrong. However, there is a way to make it come out right and that is to use leading zeros. If we convert the numbers to 0057, 0301, and 0064 we haven't actually changed any of the numbers because leading zeros don't actually mean anything, but if you sort them as plain text strings you will see that thet are actually put in the right order and we get 0057, 0064 and 0301.

A date stored in a ListBox can be copied into a Date variable and then converted into a number and put back into the ListBox. RealBasic actually stores dates internally as the total number of seconds that have elapsed since January 1st, 1904. I'm sure there must be a reason for this particular date, but if there is, it's not obvious. Anyway, what we need to do is to replace each date like "Friday, 20 July 2007" or "20/06/07" in the ListBox with the total number of seconds. The ListBox can then be sorted. Once this has been done, we can convert the total number of seconds back into the date string we had before.

In the screen shot above is a simplke application that has a ListBox containing dates. Clicking the Sort button will sort the listbox by the dates in the first column. Here is the code on the Sort button:

'sort the listbox
dim i as integer
dim d as new date
'change date to number
for i=0 to ListBox1.ListCount-1
  if ParseDate(ListBox1.Cell(i,0),d) then
    ListBox1.Cell(i,0)=Format(d.TotalSeconds,"00000000000.0")
  end if
next
'sort
ListBox1.SortedColumn=0
ListBox1.ColumnSortDirection(0)=1
ListBox1.Sort
'change number to date
for i=0 to ListBox1.ListCount-1
  d.TotalSeconds=val(ListBox1.Cell(i,0))
  ListBox1.Cell(i,0)=d.LongDate
next

First we go through each item in the ListBox and use ParseDate() to convert each date string into a date variable d. The date string is replaced with d.TotalSeconds and the Format() function is used to add leading zeros. Now we can sort the ListBox by the column containing the date. When this is done, we can change the number back into a date string again. It works well and it's easy to impliment.

Optimise for speed
It should be fairly obvious from the code how it works because it's actually quite a simple process. It is also very fast and on my Macbook a ListBox containing 5,000 dates can be sorted in around one second. If this is not fast enough for you, then there is a very simple way of speeding up the sort process. You have an extra column in the ListBox that contains the date as a number and whenever you store a date in the ListBox you also store the total seconds. The user doesn't need to be able to see this and the column can be hidden. When you set the widths of the ListBox columns with ListBox1.ColumnWidths just set the column containing the total seconds to zero. after all, you can't see anything that has zero width!

In the example program in the screen shot above, here's how I fill the ListBox with random dates:

dim d as new Date
dim r as new Random
dim i as integer
'hide TotalSeconds column
ListBox1.ColumnWidths="50%,0%,30%,20%"
'fill listbox with random dates
for i=0 to 100
  d.Year=r.InRange(1904,2008)
  d.Month=r.InRange(1,12)
  d.Day=r.InRange(1,28)
  ListBox1.AddRow ""
  ListBox1.Cell(i,0)=d.LongDate
  ListBox1.Cell(i,1)=Format(d.TotalSeconds,"00000000000.0")  'so we can sort by date
next

Since the dates are also stored as the total seconds string, the code on the Sort button is simply this:

ListBox1.SortedColumn=0
ListBox1.ColumnSortDirection(0)=1
ListBox1.Sort

Date problems
RealBasic stores dates internally as the number of seconds since 1st Jan 1904. That's zero, so dates before this are negative numbers. These would mess up the sort method, so if you need to sort a ListBox containing dates earlier than 01/01/1904 you'll need to somehow get rid of the negative numbers. You could simply add a constant to each date, such as 100 or 200 years, to make the numbers positive back to 1804 or 1704. Then they can be sorted using the method above.

There's a limit to the number of seconds a date variable can hold and the RealBasic documentation says: "Very large values of TotalSeconds will cause the value to be set to zero and dates prior to 1 January 1601 may not be represented correctly." Bear this in mind when working with dates.