How to display matching data between two different list box

[ges]

I try to find the matching data between two listbox.
Example:

ListBoxA.Column(0) Value = 2,5,12,16,25
ListBoxB.Column(0) Value = 3,7,14,16,35

The matching data between listBoxA and listBoxB is "16"
I try to display this in the label.

I create command button, with on click event, as follow:
If ListBoxA.Value = ListBoxB.Value Then
lblShow.Caption = "Matching"
Else
lblShow.Caption = "No Match"
End If

It did not register the same value, it simply show "No Match"

I also try:
If ListBoxA.Column(0).Value = ListBoxB.Column(0).Value Then
lblShow.Caption = "Matching"
Else
lblShow.Caption = "No Match"
End If

It give me an error.

Can someone help me with the syntax? The best if I could just show the data
which is "16 " in the label but if not possible even show either "matching"
or "no match" help a lot.

Thanks in advance for any help.

Ges

 

[John W. Vinson]

I try to find the matching data between two listbox.
Example:

ListBoxA.Column(0) Value = 2,5,12,16,25
ListBoxB.Column(0) Value = 3,7,14,16,35

The matching data between listBoxA and listBoxB is "16"
I try to display this in the label.

A Listbox is not a data repository. It's a display tool!

Where are these listboxes getting their data? A table, a list of values? If
the latter how is it being set?

This might be possible using VBA code to loop through each listbox's Items
collection but I really think you're using the wrong tool for the task!

 

[Piet Linden]

I try to find the matching data between two listbox.

Far and away, the easiest way to do this is if you have the
controlsources of the two listboxes be something other than value
lists. Then you can just use an inner join.

SELECT tblA.col1
FROM tblA INNER JOIN tblB ON tblA.col1=tblB.col2;

all non-matching records in both tables will excluded by the join.

If you absolutely must loop through the list, then it's a lot more
work.

If you really want to do it this way, this works:

Option Compare Database
Option Explicit

Private Sub cmdCompareLBXContents_Click()
Dim intListA As Integer, intListB As Integer
Dim strMatches As String

For intListA = 0 To Me.List0.ListCount - 1
For intListB = 0 To Me.List2.ListCount - 1
If Me.List0.ItemData(intListA) = Me.List2.ItemData
(intListB) Then
If Len(strMatches) = 0 Then
strMatches = Me.List0.ItemData(intListA)
Else
strMatches = strMatches & ", " & Me.List0.ItemData
(intListA)
End If
End If
Next intListB
Next intListA

Me.txtMatches = strMatches
End Sub

 

[ges via AccessMonster.com]

John and Piet,
Thanks for your reply. The listboxes got information from table. And being
display in form based on the user selections on a couple of combo boxes. I
will try to use the loop with VBA, Thanks again for your help.

Ges

I try to find the matching data between two listbox.
Example:

[quoted text clipped - 4 lines]

The matching data between listBoxA and listBoxB is "16"
I try to display this in the label.

A Listbox is not a data repository. It's a display tool!

Where are these listboxes getting their data? A table, a list of values? If
the latter how is it being set?

This might be possible using VBA code to loop through each listbox's Items
collection but I really think you're using the wrong tool for the task!

 

[John W. Vinson]

John and Piet,
Thanks for your reply. The listboxes got information from table. And being
display in form based on the user selections on a couple of combo boxes. I
will try to use the loop with VBA, Thanks again for your help.

I'd create a Query instead; since the data is already in a table you should be
able to create a query using the same criteria as you're using for the
listbox. Since we have no way to know what those criteria might be it's a bit
hard to suggest specifics.

 

[ges via AccessMonster.com]

What I really try to do here is I have a table has around 1,000 + rows and 23
colums/fields. All contains numbers. I have to add the numbers from each
field with formula : A2 + B3 = C5. (A2 and B3 is the number/value from each
cell in the table).

So I try to create a form that user can select the value of A2 and B3 and
display the calculation result which is C5 in the listbox.
The main purpose of this is to look up value in all those 23 fields that
match the value listed in C5).
Since it's a lot of data, and C5 change all the time (depend on value A2 & B3)
it take time to use query to look up the value from each field (tbl.field =
C5). I use (WHERE tbl.* = C5) but it's error.
What do you think the best way I should do?

As always thank you so much for your advice.

Thanks.
Ges