Solved

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

Posted on 2008-10-14
5
290 Views
Last Modified: 2012-05-05
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
Comment
Question by:Alphacircuits
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22718412
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
 
LVL 77

Expert Comment

by:peter57r
ID: 22718418
Sorry -ignore that -  I'm having some 'post too-early' problems this morning..

I'll post again in a minute.


0
 
LVL 77

Accepted Solution

by:
peter57r earned 350 total points
ID: 22718451
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
 

Author Comment

by:Alphacircuits
ID: 22720811
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
 
LVL 77

Expert Comment

by:peter57r
ID: 22728497
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question