Sorting A Collection

[Scott]

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

[Tom van Stiphout]

Re: Sorting A Collection

On Thu, 16 Dec 2004 03:17:49 GMT, "Scott" <smiller@nospam .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.

[color=blue]
>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
>[/color]

[Steve Jorgensen]

Re: Sorting A Collection

On Wed, 15 Dec 2004 21:13:13 -0700, Tom van Stiphout <no.spam.tom774 4@cox.net>
wrote:
[color=blue]
>On Thu, 16 Dec 2004 03:17:49 GMT, "Scott" <smiller@nospam .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.[/color]

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
[color=blue]
>Another would be to use a sorting algorithm, e.g. bubblesort or qsort.[/color]

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

[Nikos Yannacopoulos]

Re: Sorting A Collection

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:[color=blue]
> 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
>
>[/color]

[Jesper F]

Re: Sorting A Collection

> I want to[color=blue]
> iterate over the collection and build a value list string for the[/color]
rowsource[color=blue]
> of a listbox.[/color]

could the BubbleSort method perhaps be used?

http://chrisrae.com/vba/routines/bubblesort.html

[Bas Cost Budde]

Re: Sorting A Collection

Jesper F wrote:
[color=blue]
> could the BubbleSort method perhaps be used?
> http://chrisrae.com/vba/routines/bubblesort.html[/color]

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

[Scott]

Re: Sorting A Collection

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@he uvelqop.nl> wrote in message
news:cpsm1n$nis $1@news2.solcon .nl...[color=blue]
> Jesper F wrote:
>[color=green]
> > could the BubbleSort method perhaps be used?
> > http://chrisrae.com/vba/routines/bubblesort.html[/color]
>
> 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[/color]

[Bas Cost Budde]

Re: Sorting A Collection

Scott wrote:[color=blue]
> 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?[/color]

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

[Scott]

Re: Sorting A Collection

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.It em(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@he uvelqop.nl> wrote in message
news:cpt36v$olk $1@news2.solcon .nl...[color=blue]
> Scott wrote:[color=green]
> > The collection will have ten items at most. How can I apply the[/color][/color]
bubblesort[color=blue][color=green]
> > routine to rearranging the dates in the collection to chronological[/color][/color]
order?[color=blue]
>
> 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[/color]

[Marshall Barton]

Re: Sorting A Collection

Scott wrote:
[color=blue]
>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?[/color]


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(lngVa lue)
Dim K As Long

If colMySorted.Cou nt > 0 Then
For K = 1 To colMySorted.Cou nt
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]

[Scott]

Re: Sorting A Collection

Marsh,

Thank you very much for responding!

Could you please explain the logic of your code.

Thanks,

Scott


"Marshall Barton" <marshbarton@wo wway.com> wrote in message
news:v4f4s0pn5d t3pgpobrcap4opr f5n8ti291@4ax.c om...[color=blue]
> Scott wrote:
>[color=green]
> >I have a collection where the items in the collection are dates. I want[/color][/color]
to[color=blue][color=green]
> >iterate over the collection and build a value list string for the[/color][/color]
rowsource[color=blue][color=green]
> >of a listbox. The dates in the collection are not in chronological order.[/color][/color]
Is[color=blue][color=green]
> >there a way to first sort the collection and put the dates in[/color][/color]
chronological[color=blue][color=green]
> >order before creating the value list string? Or, how would I iterate over
> >the collection pulling out the dates in chronological order?[/color]
>
>
> 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(lngVa lue)
> Dim K As Long
>
> If colMySorted.Cou nt > 0 Then
> For K = 1 To colMySorted.Cou nt
> 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][/color]

[Scott]

Marsh - Never Mind. I Got It!

Marsh,

I'm dense! Couldn't figure out where lngValue was coming from :(

This will work great by sorting as items are added. BTW, there should be
only one comma before K since we are using the Before parameter.

Thanks again,

Scott


"Marshall Barton" <marshbarton@wo wway.com> wrote in message
news:v4f4s0pn5d t3pgpobrcap4opr f5n8ti291@4ax.c om...[color=blue]
> Scott wrote:
>[color=green]
> >I have a collection where the items in the collection are dates. I want[/color][/color]
to[color=blue][color=green]
> >iterate over the collection and build a value list string for the[/color][/color]
rowsource[color=blue][color=green]
> >of a listbox. The dates in the collection are not in chronological order.[/color][/color]
Is[color=blue][color=green]
> >there a way to first sort the collection and put the dates in[/color][/color]
chronological[color=blue][color=green]
> >order before creating the value list string? Or, how would I iterate over
> >the collection pulling out the dates in chronological order?[/color]
>
>
> 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(lngVa lue)
> Dim K As Long
>
> If colMySorted.Cou nt > 0 Then
> For K = 1 To colMySorted.Cou nt
> 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][/color]

[Marshall Barton]

Re: Marsh - Never Mind. I Got It!

As you've probably already figured out, the Before argument
is the THIRD argument of the Add method. The second
argument is the key argument, so you really do need two
commas (unless you are using a named argument).

If you are using the Key argument (my example didn't), of
course there would only be one comma between the Key and
Before arguments (the first comma is between the Item and
Key arguments).
--
Marsh
MVP [MS Access]



Scott wrote:[color=blue]
>This will work great by sorting as items are added. BTW, there should be
>only one comma before K since we are using the Before parameter.
>
>[color=green]
>> Scott wrote:[color=darkred]
>> >I have a collection where the items in the collection are dates. I want[/color][/color]
>to[color=green][color=darkred]
>> >iterate over the collection and build a value list string for the[/color][/color]
>rowsource[color=green][color=darkred]
>> >of a listbox. The dates in the collection are not in chronological order.[/color][/color]
>Is[color=green][color=darkred]
>> >there a way to first sort the collection and put the dates in[/color][/color]
>chronologica l[color=green][color=darkred]
>> >order before creating the value list string? Or, how would I iterate over
>> >the collection pulling out the dates in chronological order?[/color]
>>
>>[/color]
>"Marshall Barton" wrote[color=green]
>> 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(lngVa lue)
>> Dim K As Long
>>
>> If colMySorted.Cou nt > 0 Then
>> For K = 1 To colMySorted.Cou nt
>> If lngValue < colMySorted(K) Then
>> colMySorted.Add lngValue, , K
>> Exit Sub
>> End If
>> Next K
>> End If
>>
>> colMySorted.Add lngValue
>>
>> End Sub[/color][/color]

[Bas Cost Budde]

Re: Sorting A Collection

Scott wrote:[color=blue]
> 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.[/color]

I think I can understand that :-).

I am a little surprised to see you use a Collection for this. A
(temporary) table would do perfectly, and then you don't have to worry
about sorting, the database will do that for you.
[color=blue]
> So I need a way to sort the dates once they are all in the
> collection. The bubblesort should work using MyCollection.It em(i). I prefer
> not to use the second collection just to avoid the extra overhead.[/color]

I am afraid the overhead in bubblesort is significantly larger, as you
have to swap items all the time.
[color=blue]
> 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.[/color]

The listbox can be based on the temp table. It makes the job easier, I
think, and less reliant on code hence less sensitive to maintenance.

--
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

[Scott]

Re: Marsh - Never Mind. I Got It!

Yes, you're right!

Thanks again for all your help!

Scott


"Marshall Barton" <marshbarton@wo wway.com> wrote in message
news:fvr4s0hrl9 dn3gsmd9nq5bbi2 ndlcj5cfu@4ax.c om...[color=blue]
> As you've probably already figured out, the Before argument
> is the THIRD argument of the Add method. The second
> argument is the key argument, so you really do need two
> commas (unless you are using a named argument).
>
> If you are using the Key argument (my example didn't), of
> course there would only be one comma between the Key and
> Before arguments (the first comma is between the Item and
> Key arguments).
> --
> Marsh
> MVP [MS Access]
>
>
>
> Scott wrote:[color=green]
> >This will work great by sorting as items are added. BTW, there should be
> >only one comma before K since we are using the Before parameter.
> >
> >[color=darkred]
> >> Scott wrote:
> >> >I have a collection where the items in the collection are dates. I[/color][/color][/color]
want[color=blue][color=green]
> >to[color=darkred]
> >> >iterate over the collection and build a value list string for the[/color]
> >rowsource[color=darkred]
> >> >of a listbox. The dates in the collection are not in chronological[/color][/color][/color]
order.[color=blue][color=green]
> >Is[color=darkred]
> >> >there a way to first sort the collection and put the dates in[/color]
> >chronologica l[color=darkred]
> >> >order before creating the value list string? Or, how would I iterate[/color][/color][/color]
over[color=blue][color=green][color=darkred]
> >> >the collection pulling out the dates in chronological order?
> >>
> >>[/color]
> >"Marshall Barton" wrote[color=darkred]
> >> 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(lngVa lue)
> >> Dim K As Long
> >>
> >> If colMySorted.Cou nt > 0 Then
> >> For K = 1 To colMySorted.Cou nt
> >> If lngValue < colMySorted(K) Then
> >> colMySorted.Add lngValue, , K
> >> Exit Sub
> >> End If
> >> Next K
> >> End If
> >>
> >> colMySorted.Add lngValue
> >>
> >> End Sub[/color][/color][/color]