Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

How to Pass (text) criteria to a query using a Multi List Box

Hello Experts,

 Using Access 2K I am trying to use a multi list box in a Form to select one or many customers and pass the selection as criteria to a query.

 I am hip to the fact that this has to be done in code since the selection has to be concatenated in order for Access to know what to do with it which leads to my question....How do I do this?

Current DB Info:

 As is, the form (frmNetBookings) has two Date inputs which pass criteria to a query. Based on that date range the listbox (lbCustomerList) identifies the possible customers from said query. After this step, what I had in mind was to Select one or many customers from the listbox and then click a "Export" button that will export the query (qryNetBookings) to an excel file. All of this works except for the Multiple criteria from the listbox.

The current select staement in the listbox RowSource property is, SELECT [qry_NetBookings1].[Customer_Name] FROM qry_NetBookings1 GROUP BY [qry_NetBookings1].[Customer_Name] ORDER BY [qry_NetBookings1].[Customer_Name];  


Thanks,
nEwT



 
0
Alphacircuits
Asked:
Alphacircuits
  • 4
1 Solution
 
peter57rCommented:
For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
0
 
peter57rCommented:
Sorry -ignore that -  I'm having some 'post too-early' problems this morning..

I'll post again in a minute.


0
 
peter57rCommented:
Modify the code below to use your own fieldname and control name.


Dim varitem, strwhere
strwhere = " Where customerfieldname IN ("
For Each varItem In me.listboxname.ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & "'" & me.listboxname.ItemData(varItem)  & "',"
             End If
Next
strwhere = left(strwhere,len(strwhere)-1)  & ")" ' remove final comma and add closing )

Your export query sql should now be set as :
strsql = " select * from qry_NetBookings1] " & strwhere
0
 
AlphacircuitsAuthor Commented:
Good morning Peter57r, thanks for the reply. I have one additional questiion...Where do place this code? Would it be an after update in the listbox?  
0
 
peter57rCommented:
You could place the code  into the exit event of the listbox - you would need to assign the the result to something though - maybe a hidden textbox on your form. (Obviously what you do with the string will affect your export query sql.)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now