Taking values from a list-box for further queries

[Timo]

Hi everybody,

I would like use the values from a multiple selection in a list-box for further queries. Therefore, I have started to use a button to control selected values from the list-box (named: Automatisierungen):


Private Sub Befehl_Click()

Dim varItem As Variant, strq As String


strq = ""

For Each varItem In Me.Automatisierungen.ItemsSelected

strq = strq & "OR qry_value.value ='" & Me.Automatisierungen.ItemData(varItem) & "' "

Next varItem

Debug.Print strq



If click on e.g. two items in my list-box (SM and SMC), I will get the following result by using “Debug.Print”:




OR qry_value.value ='SM' OR qry_value.value ='SMC'



Thus I think it basically works, but I would like to have just the results “SM” OR “SMC” without the whole code.

Do you have any ideas, what I have to change?


Best regards,
Timo

[ranman256]

Try NOT to use multi-pick list boxes, it takes a LOT of programming which may not suit well for novice programmers.
INSTEAD build a form so the user can dbl-click items in a list. This runs an append query that puts it in a table.
This table can be joined to the main data table to pull only from the picked items.

[pbaldy]

Not sure it would be a usable string, but basically you just need to drop the unwanted parts from the literal string and trim the leading OR after the string is built.

[Timo]

Thank you very much for your replies.

Unfortunately, I think I need the structure of list-boxes and multiple selections to have an appropriate layout at the end.
Maybe it’s possible to take this string into another query. I’m going to try this. Then it would be fine to have this whole code.
But I think the problem with the “OR” at the beginning remains. Do have any ideas how to connect all the selected Items with an “OR” (like I already have), but do not to start with an “OR”?
I tried to connect the code with a defined constat (a part of a query that remains always the same) with an "AND": myquery = const & strq.
But the problem is that the two strings (the two queries) will be connected with an “OR”.

[June7]

Review http://allenbrowne.com/ser-50.html

[pbaldy]

Do have any ideas how to connect all the selected Items with an “OR” (like I already have), but do not to start with an “OR”?

Like I said, trim it off after you build it:

strq = Mid(strq, 4)

[Timo]

Thanks a lot.
I'm also not sure if I will get all the wished results, but the connection of two constant queries and this dynamic selection works in this way.