SELECT query with WHERE (...) based on dynamic array?!

[perlfan]

Hi there,

currently, I am writing one column of a listbox selection into an array:

Zähler = Forms![View Unpaid Invoices]!Liste2.ItemsSelected.Count
ReDim Preserve aVariable(Zähler)
For Each element In Forms![View Unpaid Invoices]!Liste2.ItemsSelected
 aVariable(count1) = Forms![View Unpaid Invoices]!Liste2.Column(0, element)
 count1 = count1 + 1
Next element

Now I would like to build a SELECT string where the result is shown for the corresponding listbox selection (could be 1 ID, or more), meaning: SELECT XY where InvoiceID = ARRAY(). How do I do this?? Thanks for help!!
Frank

 

[boblarson]

You would need to do this.

If the field is numeric:

Dim lngCount As Long
Dim strWhere As String
 
For lngCount = 0 To UBound(aVariable)
   strWhere = strWhere & aVariable(lngCount) & ","
Next
 
If Right(strWhere, 1) = "," Then
   strWhere = Left(strWhere, Len(strWhere)-1)
End If
 
strWhere = "[InvoiceID] In(" & strWhere & ")"

 

[perlfan]

Thanks a lot - I think you got me wrong. I'll give you an example: Let's say the array contains 111, 126 and 200 (three invoice IDs). Now I would like to build a string as follows: Select * from invoices where InvoiceID = these three numbers. I solved this with a static solution (where InvoiceID in (111,126,200)), but sometimes I need to build the select with one ID, sometimes with more. Thus, I need a dynamic solution. I hope, it's clear now. Thanks for help!!! FRANK

 

[boblarson]

Thanks a lot - I think you got me wrong. I'll give you an example: Let's say the array contains 111, 126 and 200 (three invoice IDs). Now I would like to build a string as follows: Select * from invoices where InvoiceID = these three numbers. I solved this with a static solution (where InvoiceID in (111,126,200)), but sometimes I need to build the select with one ID, sometimes with more. Thus, I need a dynamic solution. I hope, it's clear now. Thanks for help!!! FRANK

Umm, Frank did you actually try it or just see the IN and quickly dismiss it? You do NOT NEED THE EQUALS SIGN - I gave you the EXACT answer. It will work for one number or 2000 numbers. It works wonderful as I use it all of the time.

 

[perlfan]

OK - first, I took a look at your code, but as I am not an expert, I didn't really get it... :-/ YOU ARE RIGHT - it works great!!! Thanks a lot. Actually, it really does exactly what I needed. Thanks again!! FRANK