Multiple MultiSelect ListBoxes

[Patrick W via AccessMonster.com]

I have a form that uses a multiselect listbox as criteria for a query based
on the user selection(s). I would like to add another multiselect listbox to
the form so the user can make multiple selections in eithor, or both
listboxes for the query.

Below is the code I am useing for one list box, but when I try to add a
second list box I can't get it to work.

Private Sub cmdViewQuery_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria = strCriteria & "IT_Billing_Extract.InvoiceMonth =" & Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "SELECT IT_Billing_Extract.InvoiceMonth, Lookup_FNA_Department.[Roll-
Up Org], IT_Billing_Extract.BillToDept, IT_Billing_Extract.ProdServDrill2,
IT_Billing_Extract.ProdServDrill3, IT_Billing_Extract.ChargeDescription,
IT_Billing_Extract.PhoneNumber, IT_Billing_Extract.WorkUnit,
IT_Billing_Extract.UnitCost, IT_Billing_Extract.AmountBilled " & _
"FROM Lookup_FNA_Department INNER JOIN (tblInvoiceMonth RIGHT JOIN
IT_Billing_Extract ON tblInvoiceMonth.InvoiceMonth = IT_Billing_Extract.
InvoiceMonth) ON Lookup_FNA_Department.Department = IT_Billing_Extract.
BillToDept " & _
"WHERE " & strCriteria & _
"ORDER BY IT_Billing_Extract.InvoiceMonth "


CurrentDb.QueryDefs("2005 FNA Tcom Pagers Detail").SQL = strSQL

DoCmd.OpenQuery "2005 FNA Tcom Pagers Detail"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number = 5 Then
MsgBox "Must Make A Selection First", , "Make A Selection First"
Exit Sub
Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End If

End Sub

Thanks in advance for your help!!

 

[Douglas J. Steele]

Try replacing your section

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria = strCriteria & "IT_Billing_Extract.InvoiceMonth =" & Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

with something like

Dim strCriteria1 As String
Dim strCriteria2 As String

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria1 = strCriteria1 & "IT_Billing_Extract.InvoiceMonth =" &
Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria1 = Left(strCriteria1, Len(strCriteria1) - 4)

For Each varItem In Me.OtherListBox.ItemsSelected
strCriteria2 = strCriteria2 & "IT_Billing_Extract.Field2 =" & Chr(34)
& Me.OtherListBox.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 4)

strCriteria = "(" & strCriteria1 & ") AND (" & strCriteria2 & ")"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a form that uses a multiselect listbox as criteria for a query based
on the user selection(s). I would like to add another multiselect listbox
to
the form so the user can make multiple selections in eithor, or both
listboxes for the query.

Below is the code I am useing for one list box, but when I try to add a
second list box I can't get it to work.

Private Sub cmdViewQuery_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria = strCriteria & "IT_Billing_Extract.InvoiceMonth =" &
Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "SELECT IT_Billing_Extract.InvoiceMonth,
Lookup_FNA_Department.[Roll-
Up Org], IT_Billing_Extract.BillToDept, IT_Billing_Extract.ProdServDrill2,
IT_Billing_Extract.ProdServDrill3, IT_Billing_Extract.ChargeDescription,
IT_Billing_Extract.PhoneNumber, IT_Billing_Extract.WorkUnit,
IT_Billing_Extract.UnitCost, IT_Billing_Extract.AmountBilled " & _
"FROM Lookup_FNA_Department INNER JOIN (tblInvoiceMonth RIGHT JOIN
IT_Billing_Extract ON tblInvoiceMonth.InvoiceMonth = IT_Billing_Extract.
InvoiceMonth) ON Lookup_FNA_Department.Department = IT_Billing_Extract.
BillToDept " & _
"WHERE " & strCriteria & _
"ORDER BY IT_Billing_Extract.InvoiceMonth "


CurrentDb.QueryDefs("2005 FNA Tcom Pagers Detail").SQL = strSQL

DoCmd.OpenQuery "2005 FNA Tcom Pagers Detail"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number = 5 Then
MsgBox "Must Make A Selection First", , "Make A Selection First"
Exit Sub
Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End If

End Sub

Thanks in advance for your help!!

 

[Patrick W via AccessMonster.com]

THANKS Douglas,

I did all of this before except:

strCriteria = "(" & strCriteria1 & ") AND (" & strCriteria2 & ")"

This fixed my SQL statment (oops), everything works fine now.

Thank you, thank you!!

Try replacing your section

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria = strCriteria & "IT_Billing_Extract.InvoiceMonth =" & Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

with something like

Dim strCriteria1 As String
Dim strCriteria2 As String

For Each varItem In Me.SelectDate.ItemsSelected
strCriteria1 = strCriteria1 & "IT_Billing_Extract.InvoiceMonth =" &
Chr(34)
& Me.SelectDate.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria1 = Left(strCriteria1, Len(strCriteria1) - 4)

For Each varItem In Me.OtherListBox.ItemsSelected
strCriteria2 = strCriteria2 & "IT_Billing_Extract.Field2 =" & Chr(34)
& Me.OtherListBox.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 4)

strCriteria = "(" & strCriteria1 & ") AND (" & strCriteria2 & ")"

I have a form that uses a multiselect listbox as criteria for a query based
on the user selection(s). I would like to add another multiselect listbox

[quoted text clipped - 51 lines]

Thanks in advance for your help!!