使用'IN'访问VBA参数查询

Question

我有一个用于运行报表的查询表（由于一系列连接和各种其他复杂的条件 - 它必须是表格或失败，查询过多） ）。

我现在需要能够使用'IN'（从列表框中选择）过滤所使用的数据（它是总结字段）。

这是我想要的查询，[EWAList]是列表框中的选定项目，任何人都可以帮忙吗？

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; 

任何帮助表示赞赏！我的想法在这一点上

感谢就行了尖断:-)

谢谢！ Christy

Answer 1

您将需要使用VBA构建查询以获取IN列表。例如：

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