using multiple list as parameter for a report

[deb]

I have 3 list boxes and the user needs to be able to choose multiple selections from the list boxes as the parameter for a report. If they do not select any, the report should display all records.

Box 1-ManagerNames: Bob, Joe, Sue...
Box 2-ProjectStatus: Active, Hold, Complete, Rejected
Box 3-ChargeStatus: Open, Closed, teco

I have set up the query and the form but I dont know how to make it pull the report based on the list selection. I can make it work with single selections from the list box but not when the user chooses multiple items from the list box. I am pretty sure it needs to be coded, and I am not soooo good at this.

If you can help, you would be a life saver.

 

[SydneyGeek]

Hi Deb,
a similar request was dealt with here -- http://www.mrexcel.com/board2/viewtopic.php?t=62516&highlight=listbox
Code shows how to get multiple selections from a listbox into a string that can be used as a query criterion.
HTH
Denis

 

[deb]

Thanks for the reference. But when I tried it with my SQL code, nothing happened whin I clicked the button on the form.

Here is my code...
Private Sub Command8_DblClick(Cancel As Integer)
Dim strName As String
Dim strList As String
Dim strFilterText As String
Dim varItem As Variant
Dim strQuote
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database

Set dbs = CurrentDb()
strQuote = Chr$(34)
strList = ""

With Me!lstFY
For Each varItem In .ItemsSelected
strName = strQuote & .Column(0, varItem) & strQuote & ","
strList = strList & strName
Next
strFilterText = Left(strList, Len(strList) - 1)
End With
'[txtList] = strFilterText

strSQL = PARAMETERS Forms![fProjDuration]![lstFY] Text ( 255 );
SELECT DISTINCTROW tProject.BeltName, tProject.ChargeNo, tProject.SigmaPlusNo, tProject.DStart, tProject.DEnd, qTTLCostTTL.PreDHrsTTL, IIf([DEnd] Is Null,DateDiff("d",[DStart],Date()),DateDiff("d",[DStart],[DEnd])) AS DDuration, qTTLCostTTL.DHrsTTL, tProject.MEnd, IIf([MEnd] Is Null,DateDiff("d",[DEnd],Date()),DateDiff("d",[DEnd],[MEnd])) AS MDuration, qTTLCostTTL.MHrsTTL, tProject.AEnd, IIf([AEnd] Is Null,DateDiff("d",[MEnd],Date()),DateDiff("d",[MEnd],[AEnd])) AS ADuration, qTTLCostTTL.AHrsTTL, tProject.IEnd, IIf([IEnd] Is Null,DateDiff("d",[AEnd],Date()),DateDiff("d",[AEnd],[IEnd])) AS IDuration, qTTLCostTTL.IHrsTTL, tProject.CEnd, IIf([CEnd] Is Null,DateDiff("d",[IEnd],Date()),DateDiff("d",[IEnd],[CEnd])) AS CDuration, qTTLCostTTL.CHrsTTL, tProject.REnd, IIf([REnd] Is Null,DateDiff("d",[CEnd],Date()),DateDiff("d",[CEnd],[REnd])) AS RDuration, qTTLCostTTL.RHrsTTL, tProject.[Charge#Status], tProject.SigmaStatus, tProject.RejectDate, tProject.CC, First(tProject.ProjectName) AS [First Of ProjectName], Sum(tP
roject.Estimate) AS [Sum Of Estimate], tProject.FY, IIf([ProjectType] Like 1,"Regular",IIf([ProjectType] Like 2,"Admin",IIf([ProjectType] Like 3,"BeltSupport"))) AS ProjType
strSQL = strSQL & FROM tProject LEFT JOIN qTTLCostTTL ON tProject.ChargeNo = qTTLCostTTL.ChargeNo
strSQL = strSQL & GROUP BY tProject.BeltName, tProject.ChargeNo, tProject.SigmaPlusNo, tProject.DStart, tProject.DEnd, qTTLCostTTL.PreDHrsTTL, IIf([DEnd] Is Null,DateDiff("d",[DStart],Date()),DateDiff("d",[DStart],[DEnd])), qTTLCostTTL.DHrsTTL, tProject.MEnd, IIf([MEnd] Is Null,DateDiff("d",[DEnd],Date()),DateDiff("d",[DEnd],[MEnd])), qTTLCostTTL.MHrsTTL, tProject.AEnd, IIf([AEnd] Is Null,DateDiff("d",[MEnd],Date()),DateDiff("d",[MEnd],[AEnd])), qTTLCostTTL.AHrsTTL, tProject.IEnd, IIf([IEnd] Is Null,DateDiff("d",[AEnd],Date()),DateDiff("d",[AEnd],[IEnd])), qTTLCostTTL.IHrsTTL, tProject.CEnd, IIf([CEnd] Is Null,DateDiff("d",[IEnd],Date()),DateDiff("d",[IEnd],[CEnd])), qTTLCostTTL.CHrsTTL, tProject.REnd, IIf([REnd] Is Null,DateDiff("d",[CEnd],Date()),DateDiff("d",[CEnd],[REnd])), qTTLCostTTL.RHrsTTL, tProject.[Charge#Status], tProject.SigmaStatus, tProject.RejectDate, tProject.CC, tProject.FY, IIf([ProjectType] Like 1,"Regular",IIf([ProjectType] Like 2,"Admin",IIf([ProjectType] Like 3,"BeltSupport")))
strSQL = strSQL & HAVING (((tProject.FY)=[Forms]![fProjDuration]![lstFY] Or [Forms]![fProjDuration]![lstFY] Is Null));

dbs.QueryDefs.Delete "qProjDurationDayHours"
Set qdf = dbs.CreateQueryDef("qProjDurationDayHours", strSQL)

DoCmd.OpenQuery " qProjDurationDayHours"
Set qdf = Nothing
Set dbs = Nothing
End Sub




Here is my SQL on the Query...
PARAMETERS Forms![fProjDuration]![lstFY] Text ( 255 );
SELECT DISTINCTROW tProject.BeltName, tProject.ChargeNo, tProject.SigmaPlusNo, tProject.DStart, tProject.DEnd, qTTLCostTTL.PreDHrsTTL, IIf([DEnd] Is Null,DateDiff("d",[DStart],Date()),DateDiff("d",[DStart],[DEnd])) AS DDuration, qTTLCostTTL.DHrsTTL, tProject.MEnd, IIf([MEnd] Is Null,DateDiff("d",[DEnd],Date()),DateDiff("d",[DEnd],[MEnd])) AS MDuration, qTTLCostTTL.MHrsTTL, tProject.AEnd, IIf([AEnd] Is Null,DateDiff("d",[MEnd],Date()),DateDiff("d",[MEnd],[AEnd])) AS ADuration, qTTLCostTTL.AHrsTTL, tProject.IEnd, IIf([IEnd] Is Null,DateDiff("d",[AEnd],Date()),DateDiff("d",[AEnd],[IEnd])) AS IDuration, qTTLCostTTL.IHrsTTL, tProject.CEnd, IIf([CEnd] Is Null,DateDiff("d",[IEnd],Date()),DateDiff("d",[IEnd],[CEnd])) AS CDuration, qTTLCostTTL.CHrsTTL, tProject.REnd, IIf([REnd] Is Null,DateDiff("d",[CEnd],Date()),DateDiff("d",[CEnd],[REnd])) AS RDuration, qTTLCostTTL.RHrsTTL, tProject.[Charge#Status], tProject.SigmaStatus, tProject.RejectDate, tProject.CC, First(tProject.ProjectName) AS [First Of ProjectName], Sum(tProject.Estimate) AS [Sum Of Estimate], tProject.FY, IIf([ProjectType] Like 1,"Regular",IIf([ProjectType] Like 2,"Admin",IIf([ProjectType] Like 3,"BeltSupport"))) AS ProjType
FROM tProject LEFT JOIN qTTLCostTTL ON tProject.ChargeNo = qTTLCostTTL.ChargeNo
GROUP BY tProject.BeltName, tProject.ChargeNo, tProject.SigmaPlusNo, tProject.DStart, tProject.DEnd, qTTLCostTTL.PreDHrsTTL, IIf([DEnd] Is Null,DateDiff("d",[DStart],Date()),DateDiff("d",[DStart],[DEnd])), qTTLCostTTL.DHrsTTL, tProject.MEnd, IIf([MEnd] Is Null,DateDiff("d",[DEnd],Date()),DateDiff("d",[DEnd],[MEnd])), qTTLCostTTL.MHrsTTL, tProject.AEnd, IIf([AEnd] Is Null,DateDiff("d",[MEnd],Date()),DateDiff("d",[MEnd],[AEnd])), qTTLCostTTL.AHrsTTL, tProject.IEnd, IIf([IEnd] Is Null,DateDiff("d",[AEnd],Date()),DateDiff("d",[AEnd],[IEnd])), qTTLCostTTL.IHrsTTL, tProject.CEnd, IIf([CEnd] Is Null,DateDiff("d",[IEnd],Date()),DateDiff("d",[IEnd],[CEnd])), qTTLCostTTL.CHrsTTL, tProject.REnd, IIf([REnd] Is Null,DateDiff("d",[CEnd],Date()),DateDiff("d",[CEnd],[REnd])), qTTLCostTTL.RHrsTTL, tProject.[Charge#Status], tProject.SigmaStatus, tProject.RejectDate, tProject.CC, tProject.FY, IIf([ProjectType] Like 1,"Regular",IIf([ProjectType] Like 2,"Admin",IIf([ProjectType] Like 3,"BeltSupport")))
HAVING (((tProject.FY)=[Forms]![fProjDuration]![lstFY] Or [Forms]![fProjDuration]![lstFY] Is Null));


Please Please help, ray:

 

[deb]

Thank you for your help so far. I am a coder wanna be. But I have too much to learn.

I found this code that works great and is not as complicated, however, I do not know how to modify it to make it work for multiple list boxes...

Can you help me edit this code to make it work for 3 list boxes on the same form...

list box 1 - lstFY
data is FY0102 and FY0203
listbox 2 - ProjectType
data is Complete, Rejected, Closed
listbox 3 - SigmaStatus
data is Open, Rejected, Complete


Private Sub Command8_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qProjDurationDayHours")
' Loop through the selected items in the list box and build a text string
If Me!lstFY.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstFY.ItemsSelected
strCriteria = strCriteria & "tDuration.FY = " & Chr(34) _
& Me!lstFY.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "tDuration.FY Like '*'"
End If
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM tDuration " & _
"WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "rProjDurationDayHoursq", acViewPreview
' Empty the memory
Set db = Nothing
End Sub


Thanks in advance