Select statement includes reserved word error

[PaulA]

Hi, all -

I am seeking to open a query through VBA which uses a multiselection list box as a parameter. I got the code primarily from searching online.

I'm getting the "The Select statement includes a reserved word or an argument name that is misspelled or missing" etc. I'm not finding anything that seems reserved or whatever so extra eyes would be very much appreciated.

Below is the code:

Set db = CurrentDb()
    Set qdf = db.QueryDefs("qrySWCaseCounts")
    Set ctl = Me.lstEmployees
    For Each varItem In ctl.ItemsSelected
    strCriteria = strCriteria & "," & Me!lstEmployees.ItemData(varItem) & ""
    Next varItem
    End If

    strCriteria = Right(strCriteria, Len(strCriteria) - 1)

    strSQL = "SELECT P.StaffID, D.ReportDate AS AsOf, Count(P.PtID) AS Day_Case_Count " & _
    "FROM tblCaseAssign AS P INNER JOIN tblDates_Reports AS D ON P.StartDate <= D.ReportDate AND P.EndDate >= D.ReportDate " & _
    "WHERE P.StaffID IN(" & strCriteria & ") " & _
    "GROUP BY P.StaffID, D.ReportDate " & _
    "ORDER BY P.StaffID, D.ReportDate; "

    Debug.Print strSQL
    qdf.sql = strSQL

    DoCmd.OpenQuery "qrySWCaseCounts"
    Set db = Nothing
    Set qdf = Nothing

Any help would be appreciated.

Thanks.

 

[static]

Add spaces to the end of each line.

The line Debug.Print strSQL writes the sql to the immediate window for you to look at.

 

[Ranman256]

Most people waste time with vb code and multi select lists.
The easy way is to use the list to dbl-click to add the item to a 'pick table', using an append query.

Then simply Join the pick table to the data table.

 

[PaulA]

Thanks to you both -

I tried adding a spaces and it still isn't working; I realized that some SQL was missing from my original so I inserted plus added the spaces.

The idea of a pick table sounds intriguing but for now, if I can get the other to work that would be best for present.

thanks.

 

[PaulA]

I got the query to work using the pick table method.

Thanks again.

 

[missinglinq]

One problem, with your original code, was that, in VBA, this line

Set ctl = Me.lstEmployees

is the same as

Set ctl = Me.lstEmployees.Value

because Value is the Default Property for Listboxes...and when the Multi-Select Property for a Listbox is set to anything other than None (as is the case, here) the Listbox has no Value Property!

Dropping that line, and replacing

For Each varItem In ctl.ItemsSelected

with

For Each varItem In Me.lstEmployees.ItemsSelected

would resolve that.

Linq ;0)>

 

[PaulA]

Thanks to you all. I ended up using an combination of using a pick table and the SQL directly in the query, not supplied by VBA. It works the way I want it.