Unbound MultiSelect List Filter

Posted on 2014-01-28
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 (Access MVP)
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.
Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.


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 (Access MVP)
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 (Access MVP)
Dale Fye (Access MVP) earned 250 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 250 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 (Access MVP)
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

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

728 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