Access VBA parameter query using 'IN'

Question

Hi all,

I have a make table query that is used to run a report (due to a series of joins and various other complicated criteria - it has to be a table or it fails with too many queries).

I now need to be able to filter the data used (it is summing fields) using an 'IN' with selections from a list box.

Here is the query I want, with [EWAList] being the selected items in the list box, can anyone help?

SELECT C.Num, P.Name, E.Phase, C.Code, 
       Sum(C.Hours) AS Hours, Sum(C.Total) AS Total, 
       Sum(TotalForC.Total) AS ETotal, E.E_Date     
INTO CData
FROM (P INNER JOIN 
     (C INNER JOIN E ON C.EId=E.EId) 
                     ON P.Number=E.Num) 
        INNER JOIN TotalForC ON E.EId=TotalForC.EId    
WHERE (((C.DateCreated)>=[mydate])) 
And E.E_Date<>'0' 
And E.E_Date<>'' 
AND E.E_Number IN ([EWAList])    
GROUP BY C.Num, P.Name, E.Phase, C.Code, E.E_Date;

Any help is appreciated! I'm out of ideas at this point

Thanks for the tip on the line breaks :-)

Thanks! Christy

Answer 1

You will need to use VBA to build the query to get the IN list. For example:

Dim qdf As QueryDef

'' ListBox is the name of your listbox, the item you are looking for is
'' in column 0 (first column) and it is numeric

For Each itm In Me.ListBox.ItemsSelected
  EWAList = EWAList & "," & Me.ListBox.Column(0, itm)
Next

strSQL = "SELECT C.Num, P.Name, E.Phase, C.Code, " _
   & "Sum(C.Hours) AS Hours, Sum(C.Total) AS Total, " _
   & "Sum(TotalForC.Total) AS ETotal, E.E_Date " _
   & "INTO CData " _
   & "FROM (P INNER JOIN " _
   & "(C INNER JOIN E ON C.EId=E.EId) " _
   & "ON P.Number=E.Num) " _
   & "INNER JOIN TotalForC ON E.EId=TotalForC.EId " _
   & "WHERE (((C.DateCreated)>=[mydate])) " _
   & "And E.E_Date<>'0' " _
   & "And E.E_Date<>'' " _
   & "AND E.E_Number IN (" & Mid(EWAList,2) & ") " _
   & "GROUP BY C.Num, P.Name, E.Phase, C.Code, E.E_Date"

Set qdf = CurrentDB.QueryDefs("NameOfAnExistingQuery")
'' Permanentlt overwrite the sql of the query
qdf.SQL = strSQL