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 119

Expert Comment

by:Rey Obrero
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.

Author Comment

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

Range selector
LVL 119

Expert Comment

by:Rey Obrero
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?
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.


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 119

Accepted Solution

Rey Obrero 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

910 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

20 Experts available now in Live!

Get 1:1 Help Now