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
  • 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 47

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.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


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 47

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 47

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 47

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
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…

792 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