Solved

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

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

Technology Partners: 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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

752 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