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

 

[Nikos Yannacopoulos]

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

 

[Jesper F]

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

 

[Bas Cost Budde]

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?

 

[Scott]

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]

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

 

[Scott]

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

 

[Marshall Barton]

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

 

[Scott]

Marsh,

Thank you very much for responding!

Could you please explain the logic of your code.

Thanks,

Scott

 

[Scott]

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]

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

 

[Bas Cost Budde]

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.

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.

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.

I am afraid the overhead in bubblesort is significantly larger, as you
have to swap items all the time.

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.

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.

 

[Scott]

Yes, you're right!

Thanks again for all your help!

Scott

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]

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.

want
to order.
Is

 

[Scott]

I decided to use a collection rather than a temp table out of concern that a
temp table would cause bloat of the database file. Numerous courses are
scheduled each month so there would be a lot of adding records and deleting
records to and from the temp table.

Thanks for all your help and hope you have a Merry Christmas and a Happy New
Year!

Scott

 

[Bas Cost Budde]

I decided to use a collection rather than a temp table out of concern that a
temp table would cause bloat of the database file. Numerous courses are
scheduled each month so there would be a lot of adding records and deleting
records to and from the temp table.

Nevertheless... if you do add these dates to a table in the end, why
sort them?

You could add the dates to the schedule table immediately, together with
a (bool?) indicator it's "on the add". When course data has been
completed, clear the flag; when the course is cancelled, delete the
flagged records. Or so.

If you do a lot of scheduling anyway, why bother with the database
bloat? You can compact it at regular intervals if you wish.

All this being said...

I tried to put a sorting procedure here but I hesitate. I suggest you
use an Array instead, and do sorted insertion. That is, when the user
clicks a date in the calendar, you insert it into the array:

* increase the array size with 1
* starting at the bottom (minus one), compare the date entered to the
date at the position
* if the new date is before this one, move the array date one down and
keep looping
* if the new date is *after* this one, write it in the position next to
the current (that contains a copy of the next-next position which you
have copied down in the step before) and stop looping

I think it is easy to wrap this in a Class with an Add method (taking a
date as parameter, which gets inserted in the correct position) and a
Store method (taking a table name and a field name as parameters,
basically running an INSERT query on the table filling the field with
the dates stored in the class).

Using a class this way keeps your code clear.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

As an experiment, I once created a collection sorting routine for
Integers. Perhaps you can modify & use this to sort dates. The other
poster's recommendations are, perhaps, best for speed & maintenance, but
this was fun to code.

The class "CSortableIntegers" was a class I made so I could put integers
in a collection. It just had a public integer variable named "value."

Since collections only hold strings or objects are you storing the date
as a string value? If so it should be formatted YYYYMMDD so it sorts
correctly.

==== begin code ====

Sub CollectionIntShellSort(col As Collection)
' Purpose:
' Sort a collection of CSortableIntegers objects.
' Source:
' Translation of Shell sort from
' Data Management Techniques
' by John P. Grillo & J. D. Robertson
' Wm. C. Brown Company Publishers, Dubuque, IA, 1981, p.35
'
' Requirements:
' Collection item must have a readable "value" property,
' which is the sorting value.
' Note:
' Only works on 1 based arrays (collections).
' Think of items in the collection as a stack of plates:
' The top plate is the last item in the collection.
' If you pull a plate out of the middle the plate
' above it will come down & take it's place (index #).
' In:
' col The collection to sort
' Out:
' col Sorted
' Created:
' mgf 25apr99
' Modified:
'

Dim swapped As Boolean ' Swap flag
Dim i As Integer ' Right index
Dim j As Integer ' Left index
Dim m As Integer ' Mid-point index
Dim n As Integer ' Number of total items
Dim p As Integer ' Population count for current scan.

' Objects to swap.
Dim leftObj As Object
Dim rightObj As Object

n = col.Count
p = n
Do While p > 1
p = p \ 2
m = n - p
Do
swapped = False
For j = 1 To m
i = j + p
If col(j).value > col(i).value Then
' Save values & then swap their position in
collection.
Set leftObj = col(j)
Set rightObj = col(i)
col.Remove j
col.Add rightObj, , j 'Before:=j
col.Add leftObj, , i 'Before:=i
col.Remove i + 1
swapped = True
End If
Next j
Loop Until Not swapped
Loop

End Sub

==== end code ====

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQcMm3IechKqOuFEgEQJVFACdGhY5U5PPOBKLWkZ2eX+3kjrDWckAoP0y
j8+m0JMhX3H+SA7VyKQt/3wc
=Pv6b
-----END PGP SIGNATURE-----

 

[Tony Toews]

I decided to use a collection rather than a temp table out of concern that a
temp table would cause bloat of the database file.

See the TempTables.MDB page at my website which illustrates how to use a temporary
MDB in your app. http://www.granite.ab.ca/access/temptables.htm

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm