Solved

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

Posted on 2008-10-14
5
284 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now