Sorting A Collection

#1

I have a collection where the items in the collection are dates. I want to
iterate over the collection and build a value list string for the rowsource
of a listbox. The dates in the collection are not in chronological order. Is
there a way to first sort the collection and put the dates in chronological
order before creating the value list string? Or, how would I iterate over
the collection pulling out the dates in chronological order?

Thanks!

Scott

#2

On Thu, 16 Dec 2004 03:17:49 GMT, "Scott" <smiller (AT) nospam (DOT) please>
wrote:

One idea would be to use a disconnected ADO recordset to hold your
collection. You can create one out of thin air. They are sortable.

Another would be to use a sorting algorithm, e.g. bubblesort or qsort.

-Tom.

Quote:

I have a collection where the items in the collection are dates. I want to
iterate over the collection and build a value list string for the rowsource
of a listbox. The dates in the collection are not in chronological order. Is
there a way to first sort the collection and put the dates in chronological
order before creating the value list string? Or, how would I iterate over
the collection pulling out the dates in chronological order?

Thanks!

Scott

#3

On Wed, 15 Dec 2004 21:13:13 -0700, Tom van Stiphout <no.spam.tom7744 (AT) cox (DOT) net>
wrote:

Quote:

On Thu, 16 Dec 2004 03:17:49 GMT, "Scott" <smiller (AT) nospam (DOT) please
wrote:

One idea would be to use a disconnected ADO recordset to hold your
collection. You can create one out of thin air. They are sortable.

That was going to be my suggestion as well. I've used that, and works very
nicely. Technically, though, those are called Fabricated Recordsets, not
Disconnected Recordsets

Quote:

Another would be to use a sorting algorithm, e.g. bubblesort or qsort.

I've done that, too. Use the ADO technique <g>.

#4

Scott,

What is your "collection"? Is it not a table or a query? I would assume
it is, in which case all you need to do is make the listbox's rowsource
proprty a query (instead of a value list) to read from the table (or
existing query), and sort on date. No coding required.

HTH,
Nikos

Scott wrote:

Quote:

I have a collection where the items in the collection are dates. I want to
iterate over the collection and build a value list string for the rowsource
of a listbox. The dates in the collection are not in chronological order. Is
there a way to first sort the collection and put the dates in chronological
order before creating the value list string? Or, how would I iterate over
the collection pulling out the dates in chronological order?

Thanks!

Scott

#5

Quote:

I want to
iterate over the collection and build a value list string for the
rowsource
of a listbox.

could the BubbleSort method perhaps be used?
http://chrisrae.com/vba/routines/bubblesort.html

#6

Jesper F wrote:

Quote:

could the BubbleSort method perhaps be used?
http://chrisrae.com/vba/routines/bubblesort.html

Unless the array is relatively small, Bubblesort has the awful O(N^2)
running time. Do you want to create a Quicksort function too?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

#7

The collection will have ten items at most. How can I apply the bubblesort
routine to rearranging the dates in the collection to chronological order?

Thanks for staying with me on this!!

Scott

"Bas Cost Budde" <b.costbudde (AT) heuvelqop (DOT) nl> wrote

Quote:

Jesper F wrote:

could the BubbleSort method perhaps be used?
http://chrisrae.com/vba/routines/bubblesort.html

Unless the array is relatively small, Bubblesort has the awful O(N^2)
running time. Do you want to create a Quicksort function too?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

#8

Scott wrote:

Quote:

The collection will have ten items at most. How can I apply the bubblesort
routine to rearranging the dates in the collection to chronological order?

One other question first: how do the dates get into this collection? If
you can do any form of insertion sort, that would save a lot of effort.

I can make a sorting function for a collection "tomorrow" (whatever that
means on this earth, taking time zones into consideration--mine is
GMT+1) but maybe you can play a bit:

Create a new collection
* loop the old collection, comparing every element to the new collection
* insert the element in the right place
Return the new collection (don't forget to clean up the old one!)

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

#9

The application is for training courses and has a calendar that displays the
schedule of existing courses. I am working on a way to add new courses to
the schedule. A course may have multiple dates. The first step is that the
user clicks on any date on the calendar and that date is added to the
collection. The application can not rely on the user entering dates in
chronological order. Also a date may be deleted and another date put in its
place. So I need a way to sort the dates once they are all in the
collection. The bubblesort should work using MyCollection.Item(i). I prefer
not to use the second collection just to avoid the extra overhead.

Once the dates are in the collection on the proper order, the app will open
a data entry screen for entering the new course. The data entry form will
have a listbox that displays the dates in the collection and after the
course title and other data is entered, the user will click a button to add
the dates to a course subform.

Scott

"Bas Cost Budde" <b.costbudde (AT) heuvelqop (DOT) nl> wrote

Quote:

Scott wrote:
The collection will have ten items at most. How can I apply the
bubblesort
routine to rearranging the dates in the collection to chronological
order?

One other question first: how do the dates get into this collection? If
you can do any form of insertion sort, that would save a lot of effort.

I can make a sorting function for a collection "tomorrow" (whatever that
means on this earth, taking time zones into consideration--mine is
GMT+1) but maybe you can play a bit:

Create a new collection
* loop the old collection, comparing every element to the new collection
* insert the element in the right place
Return the new collection (don't forget to clean up the old one!)

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

#10

Scott wrote:

Quote:

I have a collection where the items in the collection are dates. I want to
iterate over the collection and build a value list string for the rowsource
of a listbox. The dates in the collection are not in chronological order. Is
there a way to first sort the collection and put the dates in chronological
order before creating the value list string? Or, how would I iterate over
the collection pulling out the dates in chronological order?

I just had a thought. The Add method has an optional Before
argument that you could use to add new items in a sorted
order.

Maybe you can adapt this to your needs:

Private colMySorted As New Collection

Public Sub AddSorted(lngValue)
Dim K As Long

If colMySorted.Count > 0 Then
For K = 1 To colMySorted.Count
If lngValue < colMySorted(K) Then
colMySorted.Add lngValue, , K
Exit Sub
End If
Next K
End If

colMySorted.Add lngValue

End Sub

--
Marsh
MVP [MS Access]