Sort a 2 dimensional string array

Question

Thank you for taking the time to read my question.

I've searched quite a bit but am not able to find a solution to sort my 2 dimensional array in MS Access 2016.

I found some posts on QuickSort by Hans Vogelaar but I'm not sure how to use it in my case.

I'm not that great at arrays so...

My array is IngrArr(17,9) as String

Any help is greatly appreciated.

Thanks

Answer 1

See if you can adapt the code from https://stackoverflow.com/questions/4873182/sorting-a-multidimensionnal-array-in-vba for your purpose.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Thanks Hans,

I tried the code and it runs without error, but it doesn't actually sort anything; ascending or descending. My Array is String, but the value that I want to sort on is a number. My understanding of arrays is that you can only have one data type, not a data type per column. I have text in other columns so I think I need to use String.

Even if it's sorted ascending I can then use a For Next loop with step -1 and just move through the array backwards.

any thoughts?

Thanks again

Answer 3

Try declaring the Array as a Variant.

---

Ken Sheridan, Stafford, England

Answer 4

Hi Ken,

I just tried it and still does not seem to make a difference. I even created a new column and put it first and populated it with numbers to see if that helped. No difference.

Thanks!

Answer 5

Here is an example of how to use QuickSortArray from the link that I posted. It creates an array of 4 rows and 3 columns (text, number, and date), and sorts it on the second column

Sub ArraySortTest()
    Dim MyArray(1 To 4, 1 To 3)
    Dim i As Long
    MyArray(1, 1) = "John"
    MyArray(1, 2) = 10
    MyArray(1, 3) = #1/1/1920#
    MyArray(2, 1) = "Paul"
    MyArray(2, 2) = 40
    MyArray(2, 3) = #1/1/1990#
    MyArray(3, 1) = "George"
    MyArray(3, 2) = 20
    MyArray(3, 3) = #1/1/1970#
    MyArray(4, 1) = "Ringo"
    MyArray(4, 2) = 30
    MyArray(4, 3) = #1/1/1950#
    Debug.Print "Before sort:"
    For i = 1 To 4
        Debug.Print MyArray(i, 1), MyArray(i, 2), MyArray(i, 3)
    Next i
    QuickSortArray MyArray, , , 2 ' sort on 2nd column
    Debug.Print "After sort:"
    For i = 1 To 4
        Debug.Print MyArray(i, 1), MyArray(i, 2), MyArray(i, 3)
    Next i
End Sub

Output:

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 6

Thanks Hans,

I'll give it another try.

I truly appreciate everyone's input/help.

Thanks!