If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using a multi-select listbox to provide query criteria
I have a listbox with a couple hundred customers. I want to be able to use
the listbox to limit my report to the selected customers. How can I do this? I did try to follow Browne's tutorial on this but the tutorial uses a two-column listbox, which doesn't apply to my case. And guidance would be greatly appreciated. |
#2
|
|||
|
|||
Using a multi-select listbox to provide query criteria
Hi AB
Generally you would have (at least) two columns in such a list box. The bound column would contain the primary key of your Customers table (CustomerID) and the second column would show the customer name. It would be normal to set the width of the first column to zero, so that the list appeared to have only one column - CustomerIDs are often "messy" numbers which are of no interest to the user. The trick is then to enumerate the ItemsSelected collection of the listbox and construct a string, so you can filter using an IN operator: Dim vItem as Variant Dim sList as string For Each vItem In lstCustomers.ItemsSelected sList = sList & lstCustomers.ItemData(vItem) & "," Next vItem ' remove the last comma If Len(sList)0 then sList = Left(sList, Len(sList)-1) DoCmd.OpenReport "MyReport", , , "[CustomerID] IN (" & sList & ")" I don't know what you've tried, but if you are still having trouble then post back the code. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "AB" wrote in message ... I have a listbox with a couple hundred customers. I want to be able to use the listbox to limit my report to the selected customers. How can I do this? I did try to follow Browne's tutorial on this but the tutorial uses a two-column listbox, which doesn't apply to my case. And guidance would be greatly appreciated. |
#3
|
|||
|
|||
Using a multi-select listbox to provide query criteria
Graham (or anyone else)
I'm not using a customer table, hence, no customer #. The actual customer is insignificant for the most part, except in the case where I want to see everything that is inbound for Customer A (which might be 200 records at any given time). The point is that I want to use multi-select listboxes for provide parameters for a report. For example, filter the report for records showing a specific destination. In these cases, the box is only one column. "Graham Mandeno" wrote: Hi AB Generally you would have (at least) two columns in such a list box. The bound column would contain the primary key of your Customers table (CustomerID) and the second column would show the customer name. It would be normal to set the width of the first column to zero, so that the list appeared to have only one column - CustomerIDs are often "messy" numbers which are of no interest to the user. The trick is then to enumerate the ItemsSelected collection of the listbox and construct a string, so you can filter using an IN operator: Dim vItem as Variant Dim sList as string For Each vItem In lstCustomers.ItemsSelected sList = sList & lstCustomers.ItemData(vItem) & "," Next vItem ' remove the last comma If Len(sList)0 then sList = Left(sList, Len(sList)-1) DoCmd.OpenReport "MyReport", , , "[CustomerID] IN (" & sList & ")" I don't know what you've tried, but if you are still having trouble then post back the code. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "AB" wrote in message ... I have a listbox with a couple hundred customers. I want to be able to use the listbox to limit my report to the selected customers. How can I do this? I did try to follow Browne's tutorial on this but the tutorial uses a two-column listbox, which doesn't apply to my case. And guidance would be greatly appreciated. |
#4
|
|||
|
|||
Using a multi-select listbox to provide query criteria
OK, so the principle is exactly the same, except chances are the field you
are filtering on is text, not numeric, so instead of: [CustomerName] in (John Doe,Fred Smith,Mary Martin) you will need to enclose each name in quotes: [CustomerName] in ("John Doe","Fred Smith","Mary Martin") So now your code looks like this: Dim vItem as Variant Dim sList as string Dim sDelim as string sDelim = Chr(34) ' a double-quote as a delimiter For Each vItem In lstCustomers.ItemsSelected sList = sList & sDelim & lstCustomers.ItemData(vItem) & sDelim & "," Next vItem ' remove the last comma If Len(sList)0 then sList = Left(sList, Len(sList)-1) DoCmd.OpenReport "MyReport", , , "[CustomerName] IN (" & sList & ")" -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "AB" wrote in message ... Graham (or anyone else) I'm not using a customer table, hence, no customer #. The actual customer is insignificant for the most part, except in the case where I want to see everything that is inbound for Customer A (which might be 200 records at any given time). The point is that I want to use multi-select listboxes for provide parameters for a report. For example, filter the report for records showing a specific destination. In these cases, the box is only one column. "Graham Mandeno" wrote: Hi AB Generally you would have (at least) two columns in such a list box. The bound column would contain the primary key of your Customers table (CustomerID) and the second column would show the customer name. It would be normal to set the width of the first column to zero, so that the list appeared to have only one column - CustomerIDs are often "messy" numbers which are of no interest to the user. The trick is then to enumerate the ItemsSelected collection of the listbox and construct a string, so you can filter using an IN operator: Dim vItem as Variant Dim sList as string For Each vItem In lstCustomers.ItemsSelected sList = sList & lstCustomers.ItemData(vItem) & "," Next vItem ' remove the last comma If Len(sList)0 then sList = Left(sList, Len(sList)-1) DoCmd.OpenReport "MyReport", , , "[CustomerID] IN (" & sList & ")" I don't know what you've tried, but if you are still having trouble then post back the code. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "AB" wrote in message ... I have a listbox with a couple hundred customers. I want to be able to use the listbox to limit my report to the selected customers. How can I do this? I did try to follow Browne's tutorial on this but the tutorial uses a two-column listbox, which doesn't apply to my case. And guidance would be greatly appreciated. |
Thread Tools | |
Display Modes | |
|
|