Solved

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

Posted on 2008-10-14
5
286 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
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

809 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