Type mismatch issue with array (1 Viewer)

[Euler271]

I'm creating a string of primary keys, called PKs, and then converting that string to an array, called A(). I'm using a comma as the delimiter.
The problem is that I'm getting a "Type Mismatch" error on the line: A = Split(PKs, ","). Here's the code:

    Dim PKs As String
    Dim ListItem as Variant
    Dim A() As Long
    PKs = "("
    For Each ListItem In Me!ListBox.ItemsSelected: PKs = PKs & Me!ListBox.Column(0, ListItem) & ", ": Next ListItem
    PKs = Left(PKs, Len(PKs) - 2) & ")"
    A = Split(PKs, ",")

I have this same code in other areas of the project and it works just fine. Can anybody see what I'm doing wrong?

 

[pbaldy]

I don't think you want parentheses around the string. The first item will be "(123", the last will be "789)".

 

[Gasman]

Have you walked your code to see what PKs contains?

 

[Pat Hartman]

Stop the code on the last line and print the value of a to the immediate window.

What are you intending to do with the value in A?

 

[Josef P.]

[ Type Mismatch ]

Dim A() As Long ' <-- !
A = Split("1,2,3", ",")

vs.

Dim A() As String
A = Split("1,2,3", ",")

Split function (Visual Basic for Applications) | Microsoft Learn:

Returns a zero-based, one-dimensional array containing a specified number of substrings.

/edit:
I overlooked post #3, there is already a hint to the wrong data type of the array.

 

[KitaYama]

I overlooked post #3, there is already a hint to the wrong data type of the array.

I deleted my post because yours was perfect.

 

[Euler271]

Since I set A() as Long I didn't think I needed quotes. I need the items to remain Long datatypes.

When I stopped the code and printed what was in PKs I found (23456), just one primary key which is as it should be since I only selected one item in the list box. If I select two items in the list box, PKs shows two primary keys; e.g., (23456, 78912)

What am I doing with A()? I'm looping through the array and updating records based on the primary key in the array. The user can select any number of items in the list box so the array could have many elements.

 

[KitaYama]

Since I set A() as Long I didn't think I needed quotes. I need the items to remain Long datatypes.

When I stopped the code and printed what was in PKs I found (23456), just one primary key which is as it should be since I only selected one item in the list box. If I select two items in the list box, PKs shows two primary keys; e.g., (23456, 78912)

What am I doing with A()? I'm looping through the array and updating records based on the primary key in the array. The user can select any number of items in the list box so the array could have many elements.

I deleted my post because I thought Joseph's response is enough.
You don't need "(" and ")"
And
You have to change your data type

Dim A As Variant
Or
Dim A() As String

If you need Long data type you can use CLng to convert the string or variant to Long
CLng(A(0))

    Dim PKs As String
    Dim ListItem as Variant
    Dim A As Variant    ' Or Dim A() AS String
    For Each ListItem In Me!ListBox.ItemsSelected: PKs = PKs & Me!ListBox.Column(0, ListItem) & ",": Next ListItem
    PKs = Left(PKs, Len(PKs) - 1)
    A = Split(PKs, ",")

I Also deleted the space after ", "
It saves me using Trim if the datatype is string.

 

[theDBguy]

What am I doing with A()? I'm looping through the array and updating records based on the primary key in the array. The user can select any number of items in the list box so the array could have many elements.

If you're planning to do something like:

For x = 0 To UBound(A)
    CurrentDb.Execute "UPDATE TableName SET FieldName='SomeValue' WHERE PK=" & A(x), dbFailOnError
Next

Then, you should also be able to do the following:

For Each ListItem In Me.ListBox.ItemsSelected
    CurrentDb.Execute "UPDATE TableName SET FieldName='SomeValue' WHERE PK=" & Me.ListBox.ItemData(ListItem)
Next

Just a thought...

 

[Josef P.]

I need the items to remain Long datatypes.

If you want to use a long array, you cannot use split.

=> Write values directly into the array:

Dim SelectedItemCount As Long
Dim ListItem  As Variant
Dim PkArray() As Long
Dim i As Long

With Me!ListBox  ' <-- bad name

   SelectedItemCount = .ItemsSelected.Count
   If SelectedItemCount = 0 Then
      ' Err.Raise ...
      ' or
      'MsgBox "..."
      Exit Sub/Function
   End If

   ReDim PkArray(SelectedItemCount - 1)

   For Each ListItem In .ItemsSelected
      PkArray(i) = .Column(0, ListItem)
      i = i + 1
   Next

End With

or as reusable code:

   Dim PkArray() As Long
   If Not FillArrayFromItemsSelected(PkArray, Me.ListBox, 0) Then
      ' Err.Raise ...
      ' or
      'MsgBox "..."
      Exit Sub/Function
   End If
   ...

'###################
Public Function FillArrayFromItemsSelected(ByRef Array2Fill As Variant, ByVal SelectListBox As ListBox, ByVal ColumnIndex As Long) As Boolean

   Dim SelectedItemCount As Long
   Dim ListItem  As Variant
   Dim i As Long

   With SelectListBox

      SelectedItemCount = .ItemsSelected.Count
      If SelectedItemCount = 0 Then
         FillArrayFromItemsSelected = False
         Exit Function
      End If

      ReDim Array2Fill(SelectedItemCount - 1)
   
      For Each ListItem In .ItemsSelected
         Array2Fill(i) = .Column(ColumnIndex, ListItem)
         i = i + 1
      Next

   End With

   FillArrayFromItemsSelected = True

End Function

 

[Gasman]

[ Type Mismatch ]

Dim A() As Long ' <-- !
A = Split("1,2,3", ",")

vs.

Dim A() As String
A = Split("1,2,3", ",")

Split function (Visual Basic for Applications) | Microsoft Learn:


/edit:
I overlooked post #3, there is already a hint to the wrong data type of the array.

That string being returned has caught me out in the past.

 

[Euler271]

Thanks to everyone for taking the time to respond.
I ended up created the string and array at the same time:

    PKs = "("
    ReDim A(Me!MyListBox.ItemsSelected.count - 1)
    For Each ListItem In Me!MyListBox.ItemsSelected
        PKs = PKs & Me!MyListBox.Column(0, ListItem) & ", "
        A(i) = Me!MyListBox.Column(0, ListItem)
        i = i + 1
    Next ListItem
    PKs = Left(PKs, Len(PKs) - 2) & ")"