Additional multi-select listbox (1 Viewer)

[jimmy0305]

I just want to ask how can I add two additional multiselect listbox from the code below...

right now I only have "lsService" on this code and I want to add "lsCampus" & "lsCategory".


any help will be greatly appreciated... Jim


Dim MyDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer, strSQL As String
Dim strWhere As String, strIN As String
Dim flgAll As Boolean


Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tblQtyPsychotropics"

For i = 0 To lsService.ListCount - 1
If lsService.Selected(i) Then
If lsService.Column(0, i) = "All" Then
flgAll = True
End If
strIN = strIN & "'" & lsService.Column(0, i) & "',"
End If
Next i

strWhere = " WHERE [service] in (" & Left(strIN, Len(strIN) - 1) & ")"

If Not flgAll Then
strSQL = strSQL & strWhere
End If


MyDB.QueryDefs.Delete "qryQuarterlyPsychotropics"
Set qdf = MyDB.CreateQueryDef("qryQuarterlyPsychotropics", strSQL)

DoCmd.OpenReport "qryQuarterlyPsychotropics", acPreview

 

[WayneRyan]

Jimmy,

This should give you a start:

Dim MyDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strTemp As String

Set MyDB = CurrentDb()
strSQL = "SELECT * FROM tblQtyPsychotropics "

'
' Handle Service ListBox
'
strWhere = ""

If Not lsService.Selected(0) Then
   For i = 0 To lsService.ListCount - 1
       If lsService.Selected(i) Then
          If Len(strWhere) = 0 Then
             strWhere = "[Service] In ('" & lsService.Column(0, i) & "'"
          Else
             strWhere = ", '" & lsService.Column(0, i) & "'"
          End If
       End If
   Next i
If Len(strWhere) > 0 Then strWhere = strWhere & ")"
'
' Handle lsCampus ListBox
'
strTemp = ""
If Not lsCampus.Selected(0) Then
   For i = 0 To lsCampus.ListCount - 1
       If lsCampus.Selected(i) Then
          If Len(strTemp) = 0 Then
             strTemp = "[Campus] In ('" & lsCampus.Column(0, i) & "'"
          Else
             strTemp = ", '" & lsCampus.Column(0, i) & "'"
          End If
       End If
   Next i
If Len(strTemp) > 0 Then strTemp  = strTemp & ")"
'
If Len(strWhere) = 0 Then
   strWhere = "Where " & strTemp
Else
   strWhere = " And " & strTemp
End If
'
' Handle lsCategory ListBox
'
strTemp = ""
If Not lsCategory.Selected(0) Then
   For i = 0 To lsCategory.ListCount - 1
       If lsCategory.Selected(i) Then
          If Len(strTemp) = 0 Then
             strTemp = "[Category] In ('" & lsCategory.Column(0, i) & "'"
          Else
             strTemp = ", '" & lsCategory.Column(0, i) & "'"
          End If
       End If
   Next i
If Len(strTemp) > 0 Then strTemp  = strTemp & ")"
'
If Len(strWhere) = 0 Then
   strWhere = "Where " & strTemp
Else
   strWhere = " And " & strTemp
End If
'
strSQL = strSQL & strWhere
'
MyDB.QueryDefs.Delete "qryQuarterlyPsychotropics"
Set qdf = MyDB.CreateQueryDef("qryQuarterlyPsychotropics", strSQL)

DoCmd.OpenReport "qryQuarterlyPsychotropics", acPreview

Wayne