Parameters with multiple selection

[Guest]

I have a query set up so that displays all the projects when passed in by its
parameter. I have it setup so that it lists all the projects that passed
through the parameter by commas and no spaces (i.e. Project1,Project2). What
im trying to do is passging it a list of projects from a list box in a form.
I am having trouble when i select more then one project in the list box. The
list box has the settings of multi select to simple. Here is my SQL code

SELECT TimeCardProjects.[Project Name], TimeCardProjects.[Task Codes],
Sum(IIf([Team Member ID]=1,[Hours],0)) AS Athaide
FROM TimeCardProjects
GROUP BY TimeCardProjects.[Project Name], TimeCardProjects.[Task Codes]
HAVING (((InStr("," & [Forms]![Another Report]![ProjectsList] & ",","," &
[TimeCardProjects].[Project Name] & ","))>0));

what am i donig wrong
Thanks to those who can help

 

[MGFoster]

I have a query set up so that displays all the projects when passed in by its
parameter. I have it setup so that it lists all the projects that passed
through the parameter by commas and no spaces (i.e. Project1,Project2). What
im trying to do is passging it a list of projects from a list box in a form.
I am having trouble when i select more then one project in the list box. The
list box has the settings of multi select to simple. Here is my SQL code

SELECT TimeCardProjects.[Project Name], TimeCardProjects.[Task Codes],
Sum(IIf([Team Member ID]=1,[Hours],0)) AS Athaide
FROM TimeCardProjects
GROUP BY TimeCardProjects.[Project Name], TimeCardProjects.[Task Codes]
HAVING (((InStr("," & [Forms]![Another Report]![ProjectsList] & ",","," &
[TimeCardProjects].[Project Name] & ","))>0));

what am i donig wrong

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The ListBox reference (Forms![Another Report]!ProjectsList) will not
return all the selected items. You will have to use a routine that
retrieves the ItemsSelected and puts them in a comma-delimited string.
Here's the routine I use:

Public Function getIDs(ByRef lst As Control, ByVal intType As Integer)
As String
' Purpose:
' Get a list of the item IDs into a comma separated string
' In:
' lst A ref to a list box control
' intType One of the dbText, dbInteger, etc. constants.
' Out:
' A string of comma-delimited IDs. Format: "1,2,3,4"
' If the intType is undefined an empty string is returned.
' Created:
' mgf 8mar2000
' Modified:
' mgf 14aug2001 Added the quote constant Q
' Replaced "FixQuotes()" function with
' "Replace()" function.
' mgf 10mar2000 Added intType selection
'
Const Q = """" ' 1 double-quote [Chr$(34)]

Dim row As Variant
For Each row In lst.ItemsSelected
Dim strIDs As String
Select Case intType
Case dbText
strIDs = strIDs & "'" & _
Replace(Replace(lst.ItemData(row), "'", "''"), _
Q, Q & Q) & "',"
Case dbDate, dbTime
strIDs = strIDs & "#" & lst.ItemData(row) & "#,"
Case dbNumeric
strIDs = strIDs & lst.ItemData(row) & ","
Case Else
' Don't know how to handle this type
Exit Function
End Select
Next row

' Return string w/o trailing comma
If Len(strIDs) > 0 Then getIDs = Left$(strIDs, Len(strIDs) - 1)

End Function

You'll have to create the SQL string dynamically & then shove the SQL
string into the QueryDef. E.g.:

dim strIDs as string

strIDs = getIDs(Me!ProjectsList, dbText)

If Len(strIDs)>0 then

strSQL = "> SELECT [Project Name], [Task Codes], " & _
"Sum(IIf([Team Member ID]=1,[Hours],0)) AS Athaide " & _
"FROM TimeCardProjects " & _
"WHERE InStr(',' & strIDs & ',', ',' & " & _
" [Project Name] & ',')>0 " & _
"GROUP BY [Project Name], [Task Codes]"

' shove SQL string into the Query Def
CurrentDB.QueryDefs("query name").SQL = strSQL

' The run the query, or use it somewhere else...

endif

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIc+zYechKqOuFEgEQKbSwCghchlA9jb+a7GvrFyLBFVto+uX7IAnjWn
VzelJl23VBSaqEQHTqKrtm5b
=Uxcx
-----END PGP SIGNATURE-----