Passing Variable to a Report 2

[jdegeorge]

Hi

I have a report that I want to filter based on user input.

I found this FAQ from Randy Smith (

http://www.tek-tips.com/faqs.cfm?spid=703&rat1=10&sfid=2657)

which got me on my way and works well with only 1 list box, but it's now getting complicated because I need several list boxes.

Here's the code so far, with only 2 variables:

[tt]
Private Sub btnPrintReport_Click()

Dim strFilter As String
Dim varItem As Variant

' loop through Area/Function listbox items selected
For Each varItem In Me!lstAreaFunction.ItemsSelected
strFilter = strFilter & "[Area/Function] = " & _
Me![lstAreaFunction].ItemData(varItem) & " OR "
Next ' continue loop

' the next bit of code will subtract out the last "OR"
If strFilter <> &quot;&quot; Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If

If IsNull(strFilter) Then
strFilter = &quot;&quot;

' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter & &quot;[Category] = &quot; & _
Me![lstCategory].ItemData(varItem) & &quot; OR &quot;
Next ' continue loop

' the next bit of code will subtract out the last &quot;OR&quot;
If strFilter <> &quot;&quot; Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
Else
'Add &quot;AND&quot; to strFilter
strFilter = strFilter & &quot; And &quot;

' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter & &quot;[Category] = &quot; & _
Me![lstCategory].ItemData(varItem) & &quot; OR &quot;
Next ' continue loop

' the next bit of code will subtract out the last &quot;OR&quot;
If strFilter <> &quot;&quot; Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
End If

DoCmd.OpenReport &quot;Area/Function&quot;, acPreview, , strFilter

End Sub[/tt]

The problem with this is that I have a number of filters that are optional. At this rate, I'll have nested If statements up the wazoo and the coding will get very ugly.

Does anyone know of an easy way to have several multi-select list boxes on a form and to pass those values to a report filter? Thanks in advance.

Jim DeGeorge

 

[dhookom]

I can't see getting around checking for selections in all of your controls without some serious design work. You can simplify some of this by setting strFilter immediately to &quot;1=1 &quot;. Then for every list box, check the number of items selected and if it is greater than 0, loop through the ItemsSelected collection. Each of these will automatically start with &quot; AND &quot;. I usually use &quot; [SomeField] In (&quot;... rather than multiple &quot; OR &quot;.

I also have a generic function that I use for all list boxes that builds the &quot; [fieldname] IN(...)&quot; This saves a lot of coding.

Duane
MS Access MVP

 

[jdegeorge]

Duane

I appreciate your attempt to help, but I don't understand what you're trying to say. And, I can't find any Access help on IN(). What would be the proper syntax?

Also, why would I set strFilter to &quot;1=1&quot;? It needs to include the field's name. Sorry to be so thick! :-(

Jim DeGeorge

 

[dhookom]

The IN () syntax is like:
Month([YourDate]) In ( 3,4,6,9,12)
or
[Color] In (&quot;Blue&quot;,&quot;Red&quot;,&quot;Green&quot
The equivalent using Or would be
[Color] = &quot;Blue&quot; Or [Color] = &quot;Red&quot; Or [Color] = &quot;Green&quot;

I start the where clause with &quot; 1=1 &quot; because it is always true and allows me to &quot;tack&quot; on more criteria without worrying about whether I need to use &quot; And &quot; or not. Since your where clause begins with &quot; 1=1 &quot; every additional condition will always use &quot; And &quot;.

Duane
MS Access MVP

 

[jdegeorge]

Duane

This sounds like good stuff and I'm going to try it. One more question for now, please. I'm trying to enter this code &quot;Set strFilter 1 = 1&quot; and it won't let me. It takes &quot;Set strFilter = 1&quot;...is that the same?

Jim DeGeorge

 

[dhookom]

Use code like:
Dim strFilter As String
Dim varItem As Variant
strFilter = &quot;1=1 &quot;
Then, every addition condition thereafter begins with &quot; AND &quot;. You don't need the code from your original posting:
If IsNull(strFilter) Then
....
End If
BTW: this would have never run anyway since strFilter would never ever have been Null. It might have been a zero length string (&quot;&quot but never null.


Duane
MS Access MVP

 

[jdegeorge]

Duane

Multi-select list boxes are new to me, as is the IN(...) functionality. I can't figure out how to loop through the ItemsSelected collection without appending them to &quot;strFilter&quot; without the &quot;OR&quot;, and can't figure out how to get the ItemsSelected collection into the &quot;(...)&quot; part of the IN syntax.

Here's as far as I got:

[tt]
Dim strFilter As String
Dim varItem As Variant

strFilter = &quot;1=1 &quot;

If Me!lstAreaFunction.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstAreaFunction.ItemsSelected
strFilter = strFilter & &quot;[Area/Function] = &quot; & _
Me!lstAreaFunction.ItemData(varItem) & &quot; OR &quot;
Next varItem ' continue loop
End If

If Me!lstCategory.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter & &quot;[Category] = &quot; & _
Me!lstCategory.ItemData(varItem) & &quot; OR &quot;
Next varItem ' continue loop
End If

DoCmd.OpenReport &quot;Area/Function&quot;, acPreview, , strFilter
[/tt]

I've searched the help files for ItemsSelected and all it showed was how to print them but didn't show how to store them in a value. I'd be deeply in your debt if you could help me with this last piece.

Jim DeGeorge

 

[dhookom]

strFilter = strFilter & &quot; And [Category] IN (&quot;
' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter Me![lstCategory].ItemData(varItem) & &quot;, &quot;
Next ' continue loop
' the next bit of code will subtract out the last &quot;OR&quot;
If Len(strFilter) > 20 Then
strFilter = Left(strFilter, Len(strFilter) - 2)
End If

If [Category] is a text field
=================================
strFilter = strFilter & &quot; And [Category] IN ('&quot;
' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter Me![lstCategory].ItemData(varItem) & &quot;', '&quot;
Next ' continue loop
' the next bit of code will subtract out the last &quot;OR&quot;
If Len(strFilter) > 21 Then
strFilter = Left(strFilter, Len(strFilter) - 3)
End If


Duane
MS Access MVP

 

[jdegeorge]

Duane

I'm getting very close. Here's my code:

[tt]
Dim strFilter As String
Dim varItem As Variant
strFilter = &quot;1=1 &quot;

strFilter = strFilter & &quot; And [Area/Function] IN (&quot;

' loop through Area/Function listbox items selected
For Each varItem In Me!lstAreaFunction.ItemsSelected
strFilter = strFilter & Me!lstAreaFunction.ItemData(varItem) & &quot;, &quot;
Next ' continue loop

If Len(strFilter) > 20 Then
strFilter = Left(strFilter, Len(strFilter) - 2)
End If

strFilter = strFilter & &quot; And [Category] IN (&quot;

' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter & Me!lstCategory.ItemData(varItem) & &quot;, &quot;
Next ' continue loop

If Len(strFilter) > 20 Then
strFilter = Left(strFilter, Len(strFilter) - 2)
End If
[/tt]

I can't figure out where to put the ending &quot&quot; for each IN. Your example showed the code for only 1 variable and I've tried but with no luck.

Here's what strFilter looks like without the ending &quot&quot;:

1=1 And [Area/Function] IN (14 And [Category] IN (2

Also, what does &quot;Len(strFilter) > 20&quot; do?

Thanks, and here's a star for your effort to this point. I can't tell you how great your help has been!!!

Jim DeGeorge

 

[jdegeorge]

Duane:

Retraction of a sort. I was able to put in the code for the missing &quot&quot;:

[tt] Dim strFilter As String
Dim varItem As Variant
strFilter = &quot;1=1 &quot;

strFilter = strFilter & &quot; And [Area/Function] IN (&quot;

' loop through Area/Function listbox items selected
For Each varItem In Me!lstAreaFunction.ItemsSelected
strFilter = strFilter & Me!lstAreaFunction.ItemData(varItem) & &quot;, &quot;
Next ' continue loop

If Len(strFilter) > 20 Then
strFilter = Left(strFilter, Len(strFilter) - 2)
End If

strFilter = strFilter & &quot And [Category] IN (&quot;

' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter & Me!lstCategory.ItemData(varItem) & &quot;, &quot;
Next ' continue loop

If Len(strFilter) > 20 Then
strFilter = Left(strFilter, Len(strFilter) - 2)
End If

strFilter = strFilter & &quot&quot;

DoCmd.OpenReport &quot;Area/Function&quot;, acPreview, , strFilter
[/tt]

But, if I don't select a value from one of the lists (the assumption being that there's no filter on that field), I get an error message because I'm appending the &quot&quot; in the wrong place.

Anywho, just wanted to let you see that I am trying! Thanks


Jim DeGeorge

 

[dhookom]

Sorry about missing the last &quot&quot;. Place the &quot&quot; in earlier. This makes sure that your strFilter has added some items. If there are not items added, the strFilter should be less than 20 characters long (depending on the number of characters in your field name). If this is the case the strFilter should be set to &quot;&quot;

strFilter = strFilter & &quot; And [Category] IN (&quot;
' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter Me![lstCategory].ItemData(varItem) & &quot;, &quot;
Next ' continue loop
If Len(strFilter) > 20 Then
strFilter = Left(strFilter, Len(strFilter) - 2) & &quot&quot;
Else
strFilter = &quot;&quot;
End If

If [Category] is a text field
=================================
strFilter = strFilter & &quot; And [Category] IN ('&quot;
' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter Me![lstCategory].ItemData(varItem) & &quot;', '&quot;
Next ' continue loop
If Len(strFilter) > 21 Then
strFilter = Left(strFilter, Len(strFilter) - 3) & &quot&quot;
Else
strFilter = &quot;&quot;
End If



Duane
MS Access MVP

 

[jdegeorge]

Duane

Your new code works better, but only if at least 1 item is selected in both list boxes. If I select from only the first, which is possible, the code for the first IN works but &quot;And [Category] IN)&quot; gets appended to strFilter because the length is way more than 20. Actually, depending on whether or not I select anything from [Area/Function], strFilter's first LEN can be anywhere from 30 to about 40, if I select a few items.

Anywho, I don't think your suggestion will ever work for multiple list boxes where one or more of the list boxes can have NO selections.

Have you ever done this for multiple list boxes and have it work? If so, can you paste that code in here? I'm thinking that I'm going to have to establish variables (A through D) for the counts of each ItemsSelected at the beginning of the code, and depending on the counts of each ItemsSelected > 0 create a series of different strings.

For example, if there are 4 list boxes, the different ItemsSelected values can be:

A>0, B>0, C>0, D>0
A=0, B>0, C>0, D>0
A>0, B=0, C>0, D>0
A>0, B>0, C=0, D>0
A>0, B>0, C>0, D=0
A=0, B=0, C=0, D>0
etc.

Based on this, I'd have to have a very lengthy series of IF...ElseIf...Else...Then statements:

If A>0, B>0, C>0, D>0 then
Loops through all 4 ItemsSelected.
ElseIf A=0, B>0, C>0, D>0 Then
Loops through ItemsSelected B, C and D
ElseIf A>0, B=0, C>0, D>0 Then
Loops through ItemsSelected A, C and D
ElseIf A>0, B>0, C=0, D>0 Then
Loops through ItemsSelected A, B and D
etc., etc.

This will be messy and very inflexible should I need to add more list boxes.

I can't think of another way around this. There has to be a simpler way to let users select query filters!!! This is going to drive me crazy!

Jim DeGeorge

 

[dhookom]

I use a generic Listbox function that I send the listbox object in to the function and if it has no items selected, returns a zero length string (&quot;&quot. If there are items selected, a statement is built that returns something like &quot; AND [FIELDA} IN ('Red', 'Green', 'Blue')&quot;. Either way, I can concatenate the returned value to any where clause of a sql statement.

My code behind a form with multiple list boxes would look like:
Dim strWHere as String
strWhere = &quot; WHERE 1=1 &quot;
strWhere = strWhere & GetLBOIn(Me.lboNEmpID)
strWhere = strWhere & GetLBOIn(Me.lboTDept)
strWhere = strWHere & GetLBOIn(Me.lboTLine)
strWhere = strWhere & GetLBOIn(Me.lboNCatID)

My list box naming convention uses the 4th character to identify the field type of the bound column and the 5th+ characters are the actual field name to use in the Where clause.

I also have a generic function for clear all or selecting all items in a list box. I don't like repeating groups of code.

Duane
MS Access MVP

 

[dhookom]

I use a generic Listbox function that I send the listbox object in to the function and if it has no items selected, returns a zero length string (&quot;&quot. If there are items selected, a statement is built that returns something like &quot; AND [FIELDA} IN ('Red', 'Green', 'Blue')&quot;. Either way, I can concatenate the returned value to any where clause of a sql statement.

My code behind a form with multiple list boxes would look like:
Dim strWHere as String
strWhere = &quot; WHERE 1=1 &quot;
strWhere = strWhere & GetLBOIn(Me.lboNEmpID)
strWhere = strWhere & GetLBOIn(Me.lboTDept)
strWhere = strWHere & GetLBOIn(Me.lboTLine)
strWhere = strWhere & GetLBOIn(Me.lboNCatID)

My list box naming convention uses the 4th character to identify the field type of the bound column and the 5th+ characters are the actual field name to use in the Where clause.

I also have a generic function for clear all or selecting all items in a list box. I don't like repeating groups of code.

Duane
MS Access MVP

 

[jdegeorge]

Duane:

Thanks for your help. Using your suggested code from before, plus the help of a few other Tek-Tip'ers, I wound up with this code:

[tt]Private Sub btnPrintReport_Click()

On Error GoTo Err_btnPrintReport_Click

Dim ctl As Control
Dim varItem As Variant
Dim strFilter As String

Dim strlstAreaFunction As String
Dim strlstCategory As String

strFilter = &quot;&quot;
strlstAreaFunction = &quot;&quot;
strlstCategory = &quot;&quot;


'Gather all List Box Variables
Set ctl = Me!lstAreaFunction

If ctl.ItemsSelected.Count > 1 Then
For Each varItem In ctl.ItemsSelected
If strlstAreaFunction = &quot;&quot; Then
strlstAreaFunction = &quot;[Area/Function] in (&quot; & ctl.ItemData(varItem) & &quot;, &quot;
Else
strlstAreaFunction = strlstAreaFunction & ctl.ItemData(varItem)
End If
Next varItem
ElseIf ctl.ItemsSelected.Count = 1 Then
For Each varItem In ctl.ItemsSelected
If strlstAreaFunction = &quot;&quot; Then
strlstAreaFunction = &quot;[Area/Function] in (&quot; & ctl.ItemData(varItem)
End If
Next varItem
End If

If strlstAreaFunction <> &quot;&quot; Then
strlstAreaFunction = strlstAreaFunction & &quot&quot;
End If



Set ctl = Me!lstCategory

If ctl.ItemsSelected.Count > 1 Then
For Each varItem In ctl.ItemsSelected
If strlstCategory = &quot;&quot; Then
strlstCategory = &quot;[Category] in (&quot; & ctl.ItemData(varItem) & &quot;, &quot;
Else
strlstCategory = strlstCategory & ctl.ItemData(varItem)
End If
Next varItem
ElseIf ctl.ItemsSelected.Count = 1 Then
For Each varItem In ctl.ItemsSelected
If strlstCategory = &quot;&quot; Then
strlstCategory = &quot;[Category] in (&quot; & ctl.ItemData(varItem)
End If
Next varItem
End If

If strlstCategory <> &quot;&quot; Then
strlstCategory = strlstCategory & &quot&quot;
End If




'Concatenate all List Box Variables
If strlstAreaFunction <> &quot;&quot; Then
If strFilter = &quot;&quot; Then
strFilter = strlstAreaFunction
Else
strFilter = strFilter & &quot; And &quot; & strlstAreaFunction
End If
End If

If strlstCategory <> &quot;&quot; Then
If strFilter = &quot;&quot; Then
strFilter = strlstCategory
Else
strFilter = strFilter & &quot; And &quot; & strlstCategory
End If
End If


DoCmd.OpenReport &quot;Area/Function&quot;, acPreview, , strFilter

Exit_btnPrintReport_Click:
Exit Sub

Err_btnPrintReport_Click:
MsgBox Err.Description
Resume Exit_btnPrintReport_Click

End Sub
[/tt]

It works if one, the other or both list boxes have values.

I put you in for a 2nd star...hope it's allowed! Thanks for all your help.

Jim DeGeorge

 

[dhookom]

Your code displays the &quot;repeating groups of code&quot; that I generally try to avoid. Below are two generic function that I use. These would cut your above code quite significantly.

Sub ClearListBox(plbo As ListBox)
Dim varItem
For Each varItem In plbo.ItemsSelected
plbo.Selected(varItem) = False
Next

End Sub

Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
&quot;lbo&quot; and be followed by one character describing the data type _
&quot;T&quot; for Text _
&quot;N&quot; for Numeric or _
&quot;D&quot; for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case &quot;T&quot; 'text data type
strDelim = &quot;'&quot; 'double quote
Case &quot;N&quot; 'numeric data type
strDelim = &quot;&quot;
Case &quot;D&quot; 'Date data type
strDelim = &quot;#&quot;
End Select

If lboListBox.ItemsSelected.Count > 0 Then
strIn = &quot; AND [&quot; & Mid(lboListBox.Name, 5) & &quot;] In (&quot;
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & &quot;, &quot;
Next
'remove the last &quot;, &quot; and add the &quot&quot;
strIn = Left(strIn, Len(strIn) - 2) & &quot &quot;
End If
BuildIn = strIn

End Function

Duane
MS Access MVP

 

[jdegeorge]

dhookom

I created a module called BuildIn and pasted in your function. How is it called? I thought that since it would become the new filter that I would replace &quot;strFilter&quot; at the end of my DoCmd print preview line. That isn't working.

This is really great stuff and I'm learning a lot. Thanks!

Jim DeGeorge

 

[dhookom]

Edited from an earlier posting:
Dim strWHere as String
strWhere = &quot; 1=1 &quot;
strWhere = strWhere & BuildIn(Me.lboNEmpID)
strWhere = strWhere & BuildIn(Me.lboTDept)
strWhere = strWHere & BuildIn(Me.lboTLine)
strWhere = strWhere & BuildIn(Me.lboNCatID)
DoCmd.OpenReport &quot;Area/Function&quot;, acPreview, , strWhere

You will need to modify the list box names and add or subtract stuff.


Duane
MS Access MVP

 

[jdegeorge]

Ah, didn't know that code was still in play! Thanks, I'll give it a whirl!

Jim DeGeorge

 

[jdegeorge]

dhookom

You rule! I have this code behind my form:

[tt] Private Sub btnPrintReport_Click()

Dim strWHere As String

strWHere = &quot; 1=1 &quot;
strWHere = strWHere & BuildIn(Me.[lboNArea/Function])
strWHere = strWHere & BuildIn(Me.lboNCategory)
strWHere = strWHere & BuildIn(Me.lboNStatus)
strWHere = strWHere & BuildIn(Me.lboNCriticality)

DoCmd.OpenReport &quot;Area/Function&quot;, acPreview, , strWHere

End Sub [/tt]

I used your BuildIn() function, and it worked perfectly, even for combinations where I left one or more of the list boxes unselected! This is so powerful! You shoud post a FAQ on this, because in searching for the anwer to this I found dozens of folks who have the same problem. Star? People should throw money!

Thanks again!

Jim DeGeorge