Using multiselect list boxes to create summary queries (1 Viewer)

[rgooch]

I realize this is an issue that has been touched on a number of times. Unfortunately, I have no been able to secure a working result, and I am desperate to find a working solution. I am running Access 2007.

The end result of the project is to create a summarized report of data types from a linked SQL database.

I have the following elements:

1. Three linked SQL databases:
   1. ClientDB
   2. ProjectDB
   3. ItemsDB
2. A form ("Job Selection Form") containing:
   1. One combo-box (single select) that lists clients from ClientDB. A change in the chosen client results in a load of ProjectsDB for the client, read from the ProjectDB database into:
   2. A simple multi-select listbox with two fields (ProjectID,ProjectName) named JobList
   3. The form works as intended.
3. A SQL query that is intended to utilize the selected ProjectID fields, and pull only the appropriate summary data from the SQL database. (broken)

I have created a Query that reads as follows:

SELECT Extension, Count(ItemID) AS FileCount, Sum(PageCount) AS Pages, Sum(ItemFileSize) AS SizeKB
FROM ItemsDB
WHERE ProjectID IN(GetCriteria())
GROUP BY Extension
ORDER BY Extension;

After examing a ton of example code, including this site, I've utilized the following for the GetCriteria() function, placed in a module:

Public Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim VarItm As Variant
    
    For Each VarItm In Forms![Job Selection Form]!JobList.ItemsSelected
        stDocCriteria = stDocCriteria & Forms![Job Selection Form]!JobList.Column(0, VarItm) & ","
    Next
    If stDocCriteria <> "" Then
        stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 1)
    Else
        stDocCriteria = "True"
    End If
    
    MsgBox "GetCriteria: " & stDocCriteria
    GetCriteria = stDocCriteria
End Function

The function works as intended when I only select one project. However, when I select multiple items, I get NONE of the results from the database.

If anyone has any suggestions, I would very, very much appreciate the assistance.

 

[rgooch]

I am running Access 2007, for the record.