Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Unbound MultiSelect List Filter

Posted on 2014-01-28
Medium Priority
Last Modified: 2014-08-05
OK, so I have a report that has fields called SizeSmall and SizeLarge.  These are presented as Small-Large to demonstrate a range.  The report is bound to a table.  I also have a form that I need to use to filter the report.  My problem is that the form is not bound to a table and uses list items in a multi-select list box that I typed in.  So for instance My end user might want to view a report that shows properties in the 1,000 - 2000sf range but also 10-15,000 sf range.  So the form is multi select.  Here is the code I have that I used in another form but it's multi-Select list is bound.

Private Sub cmdOpenReport_Click()
  On Error GoTo Err_cmdOpenReport_Click
  Dim sSF           As String
  Dim strWhere      As String
  Dim strFilter      As String
  Dim ctl           As Control
  Dim varItem       As Variant

  'make sure a selection has been made
  If Me.lstCategories.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Size"
    Exit Sub
  End If

  'add selected values to string
  Set ctl = Me.lstCategories
  For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
  Next varItem
  'trim trailing comma
  strWhere = Left(strWhere, Len(strWhere) - 1)
  'open the report, restricted to the selected items
  'If Len(sSF) > 0 Then strFilter = "[CategoryID] " & strWhere & " AND " & sSF

  'DoCmd.OpenReport "FilteredReport", acViewReport, , "tblCategories.CategoryID IN(" & strWhere & ")"
  strFilter = "tblCategories.SizeSmall IN(" & strWhere & ")"
If Len(sSF) > 0 Then strFilter = strFilter
DoCmd.OpenReport "FilteredReport", acViewReport, , strFilter

  Exit Sub

  MsgBox Err.Description
  Resume Exit_cmdOpenReport_Click

End Sub

Open in new window

I need to edit the string but am not sure how or what to change it to.
Question by:napsternova
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
  • 5
  • 4
  • 3
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39815596
try setting the Row Source of the listbox to

select distinct SizeSmall from tblCategories Order by SizeSmall

and set the RowSource type to table/query
LVL 48

Expert Comment

by:Dale Fye
ID: 39815611
I'm confused.  This line:

 strFilter = "tblCategories.SizeSmall IN(" & strWhere & ")"

implies that you actually have fields with names of SizeSmall, SizeMedium, SizeLarge,...
instead of having a single [Size] column with values of "S", "M", "L"?

If so, that table structure is going to be a nightmare to query and you are not going to be able to use code like you have listed above to filter your report.

Author Comment

ID: 39815626
The table has columns named SizeSmall and SizeLarge and are populated with values. So for instance.
Property 1 Address 2,500 3,500 comments

It's for the end user to know what size the property is.  I have a filter that works with this method utilizing a single select list box.
Industry Leaders: 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!


Author Comment

ID: 39815647
Rey, the only problem is that I need to be able to select multiple ranges.

Range selector
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39815662
then, you need to build a query to satisfy the format of the data in the field you are filtering.

post sample values from the table/fields
LVL 48

Expert Comment

by:Dale Fye
ID: 39815672
Ok, I would name those RangeMin and RangeMax, because they define the bounds of the range.  Is there a RangeID associated with those values?  What is the query that supports these ranges, or is your listbox a list of values that you entered manually?  BTW, I would do it with a table (tbl_Size_Ranges: RangeID, RangeMin, RangeMax) and then create a query like:

SELECT RangeID, Format(RangeMin, "#,000") & " to " & Format(RangeMax, "#,000") as Range
FROM tbl_Size_Range

But the important part is what does the table you are trying to filter have stored?  Is that the table that contains the SizeSmall and SizeLarge fields?  Or does it just contain a single [Size_Sqr_Ft] field?

Author Comment

ID: 39815704
Rey and Dale, well the sizes are in the same table as the unique ID identifier.  Unfortunately I didn't have the foresight to make the sizes a separate table.  I could split the table and make a new size table.  Here is the "Main" table

main table
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 39815790
Does your listbox only contain that single field, of "ranges"?

You really need to go back and create tbl_Ranges and then use that in your listbox, with a RowSource like:

           , Format(RangeMin, "#,000") & " to " & Format(RangeMax, "#,000") as Range
           , RangeMin
           , RangeMax
FROM tbl_Size_Range

Then, I would put the the actual size in your "contacts" (I think that should be Properties) table.  The down side of doing it this way is that you would have to modify your filter button to look something like:

Dim varWhere as Variant 'I use variant so I can use + to concatenate
varWhere = NULL

Set ctl = Me.lstCategories
For Each varItem In ctl.ItemsSelected
    varWhere = (varWhere + " OR ") _
                  & "([tbl_Contacts].[Size] BETWEEN " _
                  & ctl.column(2, varItem) & " AND " & ctrl.column(3, varItem) & ")"

This would build strWhere to look like:

([tbl_Contacts].[Size] BETWEEN 1000 AND 2000) OR
([tbl_Contacts].[Size] BETWEEN 2000 AND 3000) OR
([tbl_Contacts].[Size] BETWEEN 5000 AND 6000)

Author Comment

ID: 39816660
I guess the problem is sizes can be anything. Would I then have to make a sub form to enter the data?
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1000 total points
ID: 39816680
the problem is the design of your table, you have two fields (sizeSmall, sizeLarge) that should be treated as one Field (Size), the entry for this field would be Small, Large, if you want to have the information of the size measurement add another field (Measurement ) with numeric values, sample values

ID        CategoryID     Tenant                                      Size            Measurement
18                   1            Versona Accessories              Small          7000
20                   1            Versona Accessories              Large          9000

this way it will a lot easier to find the information using a query

got the idea?

Author Comment

ID: 39816740
I guess I'm not understanding.  The size is a range so small is the smallest size a tenant could use and Large is the largest a space could be.  So in a report you would have something like:

Tenant A needs a space 2,000 - 4,000 sf.  
Tenant B needs a space 2,250 - 3,500 sf.  etc

This database has over 1,000 entries but none are the same size exactly.  Is it really not possible to use it the way it is for my needs?  I can try and redo my entry form and reconfigure my database.  unfortunately this is a must have feature.  LOL
LVL 48

Expert Comment

by:Dale Fye
ID: 39817248
maybe it would help if you backed up and explained what the table your showed us the screen shot of and the filter form is supposed to be used for.

I think what has me confused is that you contact (Charming Charlies) who appears wants a unit with a minimum size of 5000SF and a maximum size of 6500 SF.

So, where does the listbox come in?  What are you trying to filter with that list?  Is it this same table (tblContacts) or is it some other table (tblProperties)?  Explain this they way you would to a non-programmer.  This statement from your original post has me confused:

"My end user might want to view a report that shows properties in the 1,000 - 2000sf range but also 10-15,000 sf range."

I just don't see how that statement relates to the sample of the table you posted.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

597 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