none
Sort a 2 dimensional string array RRS feed

  • 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

    Wednesday, November 21, 2018 9:21 PM

Answers

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

    • Marked as answer by mbrad Friday, November 23, 2018 2:00 PM
    Thursday, November 22, 2018 10:55 PM

All replies

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

    Wednesday, November 21, 2018 10:54 PM
  • 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

    Thursday, November 22, 2018 6:11 PM
  • Try declaring the Array as a Variant.

    Ken Sheridan, Stafford, England

    Thursday, November 22, 2018 6:27 PM
  • 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!

    Thursday, November 22, 2018 7:07 PM
  • 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)

    • Marked as answer by mbrad Friday, November 23, 2018 2:00 PM
    Thursday, November 22, 2018 10:55 PM
  • Thanks Hans,

    I'll give it another try.

    I truly appreciate everyone's input/help.

    Thanks!

    Friday, November 23, 2018 1:59 PM