Sort an array I have created in Access 2007

[Redhawk]

Does Access 2007 have a command to sort an array the user creates?

Would you provide an example of the syntax?

 

[Alex Dybenko]

Hi,
google for bubble sort, or search here: http://www.freevbcode.com
there are a plenty algorithms

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Redhawk]

Thanks Alex, I have written a bubble sort in the past.

I was wondering if Access has a built-in optimized sort that I could call
instead.

 

[BeWyched]

Hi Redhawk

This isn't exactly what you want but it does the job very well. The
algorithm is, reputedly, the fastest there is as it moves data through
greater steps than bubble sorts - it 'best guesses where en element should be
rather than repeatedly moving it, then moves elements in chunks. The coding
is 'borrowed' from the VBA Developer's Handbook - my bible!

Pass the array as the varArray element. the coding will return the array
sorted. You can specify the left and right limits of sort if required
(lngLeft, lngRight)

Private Sub dhQuickSort(varArray As Variant, Optional lngLeft As Long =
dhcMissing, Optional lngRight As Long = dhcMissing)

Dim i As Long, j As Long, VarTestVal As Variant, lngMid As Long

If lngLeft = dhcMissing Then lngLeft = LBound(varArray)
If lngRight = dhcMissing Then lngRight = UBound(varArray)

If lngLeft < lngRight Then
lngMid = (lngLeft + lngRight) \ 2
VarTestVal = varArray(lngMid)
i = lngLeft
j = lngRight
Do
Do While varArray(i) < VarTestVal
i = i + 1
Loop
Do While varArray(j) > VarTestVal
j = j - 1
Loop
If i <= j Then
SwapElements varArray, i, j
i = i + 1
j = j - 1
End If
Loop Until i > j
If j <= lngMid Then
Call dhQuickSort(varArray, lngLeft, j)
Call dhQuickSort(varArray, i, lngRight)
Else
Call dhQuickSort(varArray, i, lngRight)
Call dhQuickSort(varArray, lngLeft, j)
End If
End If

End Sub


Private Sub SwapElements(varItems As Variant, lngItem1 As Long, lngItem2 As
Long)

Dim varTemp As Variant

varTemp = varItems(lngItem2)
varItems(lngItem2) = varItems(lngItem1)
varItems(lngItem1) = varTemp

End Sub

 

[Redhawk]

Thanks for the code example.

I need to obtain the book you mentioned, thanks for t heads up.

 

[Redhawk]

Thanks so much for the help.

I need to obtain a copy of the book you mentioned, thanks for the heads up.

 

[Redhawk]

Since I'm not that experienced with Access I have a couple of questions
regarding the code you included:

1. Is dhcMissing a constant you have defined somewhere else and equated to
zero?
2. Am I correct to assume Optional parameters are just that and may be
omitted from the calling statement? Would this be like an overloaded
procedure which can have differing types and numbers of parameters?

 

[BeWyched]

Hi again

1. No, dhcMisssing should compile as it stands as it should be recognised
within VBA. It's value is effectively Boolean with a default value of 'true'.
2. Correct. If you don't need to limit the sort range, either, or both, of
the optional parameters can be omited. In this case the dhcMissing value is
assumed as 'true' resulting in a sort across the entire array.
3. The book is 'VBA Developers Handbook', published by Sybex, ISBN:
0-7821-2978-1, cost $50 (or £37 in real money!).

Cheers.

BW

 

[Albert D. Kallal]

Does Access 2007 have a command to sort an array the user creates?

Users don't create arrays!!!

I can't imagine you need to sort an array when you have this fantastic data
engine that can slice and dice and sort the data fro you?

Where the heck is this data coming from, and why not sort it BEFORE you
place it into the array?

Furthermore, it quite RARE that you need to use an array to hold data, since
it usually coming form a source that you CAN SORT....

It is possible that you actually do need to sort this array, but in
practice, it is VERY rare in ms-access that you have to go back to 20 year
old style pf coding and using arrays here....

You might want to expand on your problem, and it is just my spider sense
telling me that having to sort an array is a RARE need in ms-access these
days....

 

[Stuart McCall]

Does Access 2007 have a command to sort an array the user creates?

Would you provide an example of the syntax?

Take a look here:

http://www.smccall.demon.co.uk/Downloads.htm#Sequence

for an A2000 mdb which visually demonstrates 9 different sorting algorithms.
Take your pick.

 

[John Spencer]

When I try to compile the code, I get a compile error that dhcMissing is not
defined.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Redhawk]

Thanks to everyone for your comments and suggestions.