CODEWITHSUNDEEP
ms-accessms-access-2007vba
Chuck
Chuck

Reputation: 1226

Type mismatch error when comparing listboxes

 Dim lastcomp As String
        Dim qty As Integer
        Dim rs As New ADODB.Recordset
        rs.Open "select Prem1Item,Prem1Qty from [TU FAR Before VB] order by Prem1Item", accCon
        Do While Not rs.EOF
            If Not IsNull(rs(0).Value) Then
                If rs(0).Value <> "n/a" Then
                    If rs(0).Value <> "" Then
                        premlist.AddItem rs(0).Value & Format(rs(1).Value, "00")
                    End If
                End If
            End If
            rs.MoveNext
        Loop
        rs.Close
        Dim i As Integer
        Dim j As Integer
        i = 1

        For i = 1 To premlist.ListCount
            For j = 1 To finallist.ListCount
                    **If Not finallist(j) = premlist(i) Or finallist(j) = "" Then**
                        finallist.AddItem premlist(i)
                    End If
            Next j
        Next i
        AccessConnection ("Close")

    End If

I am trying to take the records and pull all of the items in Prem1Item and condense then down to not show duplicates and also get the amount from Prem1Qty and show the total of each item it finds. I was trying to put them in these listboxs and then export them to a table that has 2 columns (Premium and Sum)

I am getting error 13 Type mismatch highlighting the area I have put in Bold ("If Not finalist(j) = premlist(i) Or finalist(j) = "" Then"). My plans were to get that list populated and then fill the table to generate my report with.

Upvotes: 1

Views: 698

Answers (2)

HansUp
HansUp

Reputation: 97100

A list box object does not allow you to retrieve row values with an index value, like you would for an array, or a VBA Collection, or a recordset Fields collection, and so on.

There is probably a better way to say that, but I don't know how. But attempts such as the following will throw that "Type Mismatch" error ...

Debug.Print Me.finallist(1)
Debug.Print TypeName(Me.finallist(1))

If you want to retrieve the bound column value from each of the list box's rows, use the ItemData property.

Dim i As Long
For i = 0 To (Me.finallist.ListCount - 1)
    Debug.Print Me.finallist.ItemData(i)
Next
Debug.Print "done"

Upvotes: 2

Graham Anderson
Graham Anderson

Reputation: 1239

I think you should try adding the .value to your comparrison e.g.

finallist(j).value = premlist(i).value

Upvotes: 0

Related Questions