Empty values in array

[ECEstudent]

Hi, my code is behaving a little weird. I'm trying to store all the OrderNumber + Item combinations in 2 arrays and then because the OrderNumber column really contains 2 values I'm interested in, I splite it up and store that column's values in 2 arrays. So in total, I have 3 arrays. An array for Item, an array for Order, and an array for RepId (which is the one that I split up from the OrderNumber column).
Anyways, when I print the RepId array with the ' MsgBox PostValCol1(x) ' It prints 4 values like it's supposed to. But when I tested it again by looping through the values and just doing a MsgBox, It goes for a loooong time and that's because it has a lot of empty values in that array. Does anyone know to get rid of those empty values/not store them in the first place?

Thanks!

        Set rop = CurrentDb.OpenRecordset("Select OrderNumber, ItemNumber From dbo_EntryStructure Where (ProductNumber = '" & txtPartNumber & "') AND (ActionCode = 'I')")
        
        While rop.EOF = False
            ReDim Preserve ArrRepOrder(j)
            ReDim Preserve ArrItem(j)
            ArrRepOrder(j) = rop.Fields("OrderNumber") 'Collect RepId
            ArrItem(j) = rop.Fields("ItemNumber") 'Collect Item
            j = j + 1
            rop.MoveNext
        Wend
       ' L = ArrRepOrder
        i = 0
        r = 0
        x = 0
        
        Dim PostValCol1(1000) As String, PostValCol2(1000) As String
        Dim PreArray As String, PostArray As String, strval As String
        For x = 0 To UBound(ArrRepOrder)
        
            PostValCol1(x) = Left(ArrRepOrder(x), InStr(1, ArrRepOrder(x), "-") - 1)
            PostValCol2(x) = Mid(ArrRepOrder(x), InStr(1, ArrRepOrder(x), "-") + 1, Len(ArrRepOrder(x)))
            
            
            MsgBox PostValCol1(x)
            MsgBox PostValCol2(x)
            
        Next x
    
        L = PostValCol1

 

[pr2-eugin]

Try using a condition when storing them.. Something along the lines of..

If Len(rop.Fields("ItemNumber") & vbNullString) <> 0 Then
    ArrRepOrder(j) = rop.Fields("OrderNumber") 'Collect RepId
    ArrItem(j) = rop.Fields("ItemNumber") 'Collect Item
    j = j + 1
End If
rop.MoveNext

 

[ECEstudent]

I tried that but it's still returning null values. I'm wondering if it has something to do with this part of the code:

           PostValCol1(x) = Left(ArrRepOrder(x), InStr(1, ArrRepOrder(x), "-") - 1)
            PostValCol2(x) = Mid(ArrRepOrder(x), InStr(1, ArrRepOrder(x), "-") + 1, Len(ArrRepOrder(x)))

 

[pr2-eugin]

If it is empty it will throw errors at the lines that you have shown.. As InStr would return a 0 when the ArrRepOrder(x) is empty thus an Invalid Procedure Call or Argument error will be thrown..

So which concludes it has nothing to do with empty values..