Listbox to Criteria for Query

[trussell]

Hi Everyone,

I'm sure there is a simple answer to this, but I am stuck atm.

I have a "create report" form, where a user can set certain parameters like the date-range and what specific clients they want to look at.
I have a list box where the user can select as many clients as they want to be included in the report. Now, the report requested by my boss is strange. A textfilter will not work for this. Long story short, I need the list of selected clients to be fed into a criteria for a query.
I want to use the In() function for the query criteria.

If someone could give me some advice for how to approach this, I would greatly appreciate it!


Thanks.

[Gicu]

You need to loop through the selected items in the list box and build your IN list:

    'build IN list
Dim v, sIN as string,sSQL as string


    With Forms!frmYourForm!lstYourList
        For Each v In .ItemsSelected
            sIN = sIN & "'" & .ItemData(v) & "',"
        Next
    End With


    sIN = Left(sIN , Len(sIN ) - 1)

sSQL=SELECT tblCustomers.* FROM tblCustomers WHERE CustomerID IN (" & sIN & ");"

Cheers,
Vlad

[trussell]

Hey Vlad,

Thanks for your response!

Would I be able to use this "In List" as the criteria for Customer ID in a query?

Currently for my Criteria in a Query, I have written: In (Forms!CustomerForms!CustomerID)


Within the Form, I have a button that I want to use to set CustomerID = sIN and then run the query. The button is able to set CustomerID = sIN. However, when I try to run the query, with the criteria statement listed above, I always get a blank result!

Please let me know if you have any suggestions. Thanks!

You need to loop through the selected items in the list box and build your IN list:

    'build IN list
Dim v, sIN as string,sSQL as string


    With Forms!frmYourForm!lstYourList
        For Each v In .ItemsSelected
            sIN = sIN & "'" & .ItemData(v) & "',"
        Next
    End With


    sIN = Left(sIN , Len(sIN ) - 1)

sSQL=SELECT tblCustomers.* FROM tblCustomers WHERE CustomerID IN (" & sIN & ");"

Cheers,
Vlad

[Gicu]

No, you would need to create a custom function:

Public Function fnSelectedCustomers() as string
'build IN list
Dim v, sIN as string,sSQL as string




    With Forms!CustomerForms!lstCustomer  'this is your multiselect listbox with CustomerID as the bound column
        For Each v In .ItemsSelected
            sIN = sIN & "'" & .ItemData(v) & "',"
        Next
    End With




    sIN = Left(sIN , Len(sIN ) - 1)
fnSelectedCustomers=sIN

Now in your query use IN ( & fnSelectedCustomers() & ). Or use a hidden textbox on the form and make its control source the custom function and reference that in the IN clause.

Or build the query string in VBA like I showed you in the previous post and use that as the record source for the report.

Cheers,

[pbaldy]

Another option:

http://www.baldyweb.com/multiselect.htm

[ssanfu]

I can't tell if you (your boss) wants one report for ALL Customers or if you want one report report per Customer...

[moke123]

Here's an example Db with a generic public function I use with all my multi-select listboxes.