I'll post a bit of code I already did up for someone else. But just note that this person had the word _ALL unioned in to the Listbox so we set it up to ignore that listbox if _ALL was chosen.:
Code:
Dim strWhere As String
Dim strITLead As String
Dim strProgram As String
Dim strProjDriv As String
Dim strSponsoringBanks As String
' Build criteria string for GBT IT Lead
' if All is selected then we do not need the variable
If Me.lstITLead.ItemData(0) <> " All" Then
For Each varItem In Me.lstITLead.ItemsSelected
strITLead = strITLead & Chr(34) & Me.lstITLead.ItemData(varItem) & Chr(34) & ","
End If
Next varItem
strITLead = Left(strITLead, Len(strITLead) - 1)
strWhere = "[GBT IT LEAD] IN(" & strITLead & ") AND "
End If
' Build criteria string for Program
' if ALL is selected then no need to use any variable
If Me.lstProgram.ItemData(0) = " All" Then
For Each varItem In Me.lstProgram.ItemsSelected
strProgram = strProgram & Chr(34) & Me.lstProgram.ItemData(varItem) & Chr(34) & ","
End If
Next varItem
strProgram = Left(strProgram, Len(strProgram) - 1)
strWhere = strWhere & "[Program] IN(" & strProgram & ") AND "
End If
' Build criteria string for Project name
If Me.lstProject.ItemData(0) = " All" Then
For Each varItem In Me.lstProject.ItemsSelected
strProject = strProject & Chr(34) & Me.lstProject.ItemData(varItem) & Chr(34) & ","
End If
Next varItem
strProject = Left(strProject, Len(strProject) - 1)
strWhere = strWhere & "[Project] IN(" & strProject & ") AND "
End If
' Build criteria string for Project Driver
If Me.lstProDriver.ItemData(0) = " All" Then
For Each varItem In Me.lstProDriver.ItemsSelected
strProjDriv = strProjDriv & Chr(34) & Me.lstProDriver.ItemData(varItem) & Chr(34) & ","
End If
Next varItem
strProjDriv = Left(strProjDriv, Len(strProjDriv) - 1)
strWhere = strWhere & "[Project Driver] IN(" & strProjDriv & ") AND "
End If
' Build criteria string for SponsoringBanks
If Me.lstSpoBk.ItemData(0) = " All" Then
For Each varItem In Me.lstSpoBk.ItemsSelected
strSponsoringBanks = strSponsoringBanks & Chr(34) & Me.lstSpoBk.ItemData(varItem) & Chr(34) & ","
Next varItem
strSponsoringBanks = Left(strSponsoringBanks, Len(strSponsoringBanks) - 1)
strWhere = strWhere & "[Sponsoring Banks] IN(" & strSponsoringBanks & ")"
End If
'strip the AND off (we put one on every one of them so as to not have to test for it first)
strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 5)
' Build SQL statement
strSQL = "SELECT [GBT Project Dashboard].* FROM [GBT Project Dashboard] " & strWhere