2 list boxes used as creteria

[Guest]

I have 2 list boxes that I need to use as my creteria to run a report from a
query.
The code below worked for the first list box, how can I add the second as
part of my cretieria?
Note, the first list box contain NAME, and the second list box contain STATUS
Thanks

Private Sub Command11_Click()
Dim LBx As ListBox, Cri As String, Cri2 As String, DQ As String, itm

Set LBx = Me!List2
DQ = """"

If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If Cri <> "" Then
Cri = Cri & ", " & DQ & LBx.Column(1, itm) & DQ
Else
Cri = DQ & LBx.Column(1, itm) & DQ
End If
Next

Cri = "[NAME] In(" & Cri & ")"
Debug.Print Cri
End If

DoCmd.OpenReport "Step1_Member_Status", acViewPreview, , Cri
Set LBx = Nothing

End Sub

 

[Alex Dybenko]

Hi,
do the same to collect selected items for second listbox into Cri2 and then
make a filter like:

Cri = "[NAME] In(" & Cri & ") AND STATUS IN(" & Cri2 & ")"

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Didnt work:
Private Sub Command11_Click()
Dim LBx As ListBox, Cri As String, Cri2 As String, DQ As String, itm

Set LBx = Me!List2
DQ = """"

If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If Cri <> "" Then
Cri = Cri & ", " & DQ & LBx.Column(1, itm) & DQ
Else
Cri = DQ & LBx.Column(1, itm) & DQ
End If
Next

Set LBx = Me!List4
DQ = """"

If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If Cri2 <> "" Then
Cri2 = Cri2 & ", " & DQ & LBx.Column(1, itm) & DQ
Else
Cri2 = DQ & LBx.Column(1, itm) & DQ
End If
Next

Cri = "[Assigned Team Member] In(" & Cri & ") AND STATUS IN(" & Cri2
& ")"


Debug.Print Cri
End If

DoCmd.OpenReport "Step1_Member_Status", acViewPreview, , Cri

Set LBx = Nothing

End Sub

Hi,
do the same to collect selected items for second listbox into Cri2 and then
make a filter like:

Cri = "[NAME] In(" & Cri & ") AND STATUS IN(" & Cri2 & ")"

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I have 2 list boxes that I need to use as my creteria to run a report from
a
query.
The code below worked for the first list box, how can I add the second as
part of my cretieria?
Note, the first list box contain NAME, and the second list box contain
STATUS
Thanks

Private Sub Command11_Click()
Dim LBx As ListBox, Cri As String, Cri2 As String, DQ As String, itm

Set LBx = Me!List2
DQ = """"

If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If Cri <> "" Then
Cri = Cri & ", " & DQ & LBx.Column(1, itm) & DQ
Else
Cri = DQ & LBx.Column(1, itm) & DQ
End If
Next

Cri = "[NAME] In(" & Cri & ")"
Debug.Print Cri
End If

DoCmd.OpenReport "Step1_Member_Status", acViewPreview, , Cri
Set LBx = Nothing

End Sub

 

[Guest]

Thanks,
I got it to work, I needed to be attention to Column values.

Correct code:

Private Sub Command11_Click()
Dim LBx As ListBox, criName As String, criStatus As String, Cri As String,
DQ As String, itm
DQ = """"


Set LBx = Me!List2
If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If criName <> "" Then
criName = criName & ", " & DQ & LBx.Column(1, itm) & DQ
Else
criName = DQ & LBx.Column(1, itm) & DQ
End If
Next

criName = "[Assigned Team Member] In(" & criName & ")"
Debug.Print criName
End If


Set LBx = Me!List4
If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If criStatus <> "" Then
criStatus = criStatus & ", " & DQ & LBx.Column(0, itm) & DQ
Else
criStatus = DQ & LBx.Column(0, itm) & DQ
End If
Next

criStatus = "[Status] In(" & criStatus & ")"
Debug.Print criStatus
End If
Cri = criName & IIf(criName > "", " and ", "") & criStatus
DoCmd.OpenReport "Step1_Member_Status", acViewPreview, , Cri
Set LBx = Nothing

End Sub

 

[Guest]

I got it to work. Thanks

Correct code.