Solved

Unbound MultiSelect List Filter

Posted on 2014-01-28
12
385 Views
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_cmdOpenReport_Click:
  Exit Sub

Err_cmdOpenReport_Click:
  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.
0
Comment
Question by:napsternova
  • 5
  • 4
  • 3
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 

Author Comment

by:napsternova
Comment Utility
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.
0
 

Author Comment

by:napsternova
Comment Utility
Rey, the only problem is that I need to be able to select multiple ranges.

Range selector
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
ORDER BY RangeMin

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?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:napsternova
Comment Utility
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
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
Comment Utility
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:

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

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) & ")"
Next

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)
0
 

Author Comment

by:napsternova
Comment Utility
I guess the problem is sizes can be anything. Would I then have to make a sub form to enter the data?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
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?
0
 

Author Comment

by:napsternova
Comment Utility
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

772 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

11 Experts available now in Live!

Get 1:1 Help Now