How to loop through a dynamic jagged array?

[clotposlo3]

There's plenty of information out there showing how to loop through a jagged array when you know the dimensions, but I can't find anything that tells how to loop through dynamic jagged arrays.

Here's pseudo-code for what I want to do:

For (each element in array, dimension two)
     For (each element in array, dimension one)
         (add element two to table column)
         (add element one to table, next column)...
     Next
Next
 

So my question is how to construct the "For" statement so that I'm referencing the correct dimension? Do I need empty curly braces or empty parentheses in place of the other dimension?

Thanks in advance.

[ADezii]

1. I am a little confused as to your request, but you can reference the proper Dimension when using LBound() and UBound() to Loop thru the Array Elements, as in:
   

   Dim intD1 As Integer
   Dim intD2 As Integer
   Dim aTest(1 To 2, 1 To 4) As Integer
    
   aTest(1, 1) = 10
   aTest(1, 2) = 20
   aTest(1, 3) = 30
   aTest(1, 4) = 40
   aTest(2, 1) = 100
   aTest(2, 2) = 200
   aTest(2, 3) = 300
   aTest(2, 4) = 400
    
    
   For intD1 = LBound(aTest, 1) To UBound(aTest, 1)
     For intD2 = LBound(aTest, 2) To UBound(aTest, 2)
        Debug.Print CStr(intD1) & "/" & CStr(intD2) & ": " & aTest(intD1, intD2)
     Next
   Next

2. OUTPUT:
   

   1/1: 10
   1/2: 20
   1/3: 30
   1/4: 40
   2/1: 100
   2/2: 200
   2/3: 300
   2/4: 400

3. If my assumption is incorrect, kindly clarify.

[clotposlo3]

ADezii,

Thank you for your reply. I would like to rearrange the data from a GetRows statement into a new table. While I know there is data in at least two fields, the others may or may not have data, in which case, I don't want to add them to the new table. This makes it impossible to DIM the array to any fixed size and makes referencing the dimensions rather tricky.

This may be another case of making things hard on myself, as I've been told here before. At this point, the best option may be to give up on the jagged array and try something else, such as working with a single record at a time from GetRows instead of the whole recordset at once.

I'm sure I'll have more stupid/strange questions along the way, so thank you very much for sharing your expertise.

[ADezii]

You are quite welcome, glad to assist. Why not set Criteria on the Data Source of the Recordset to reflect only those Records that would be added to a New Table. With this approach, GetRows() would only retrieve relevant Records. Is a Make Table Query a viable option?

[clotposlo3]

ADezii,

I need all the data from the recordset (except nulls), but it will be rearranged in the new table. Some fields will be combined into a single field. Another field will contain info about the group each of the elements in the first field belong to. There will be duplicates in both fields. Once this is accomplished, I need to perform calculations on both fields to find the number of duplicates, and then perform calculations on those calculations.

I'm afraid I don't know any easy way to do what I want to do. Your suggestions are always welcome!

[ADezii]

Can you possibly Upload a subset of the Data, and the results that you would like to see from the processing of this Data? In this manner it would be crystal clear what you are trying to accomplish and a solution may be more easily arrived at.

[clotposlo3]

I'm happy to say I resolved my problem, although I did have to give up on the jagged array. It took a surprisingly small nested loop to process the entire recordset from a single GetRows statement and add it all to the new table in exactly the way I wanted.

The most helpful piece of advice was from those who told me (in other posts) that I've been making things harder on myself than they have to be. I didn't think I could do all the required processing on all the different kinds of data and get it into the new table with one fairly simple loop. That was a valuable lesson!