首页 文章

使用表单筛选交叉表查询MS Access 2010

提问于
浏览 次
1

我正在使用Access 2010.我有一个交叉表查询，我试图根据表单上的文本框的值进行筛选 . 完整的SQL如下：

PARAMETERS Forms!SelectForm!Text27 DateTime;

TRANSFORM nz(Sum([Raw Contract Data].[Action Obligation]), 0) AS [SumOfAction Obligation]

SELECT [Raw Contract Data].[NAICS Code], [Raw Contract Data].[NAICS Description], Sum([Raw
Contract Data].[Action Obligation]) AS [Total Of Action Obligation], NZ(round(avg([SumOfAction 
Obligation]),0),) AS [Average Obligation], 
nz(round(stdev([SumOfAction Obligation]),0),0) AS [StDev of CTRs], 
nz(round(var([SumOfAction Obligation]),0),0) AS [Variance of Obligations]

FROM [Raw Contract Data]

WHERE [Raw Contract Data].[Date Signed]  <= Forms!SelectForm!Text27

GROUP BY [Raw Contract Data].[NAICS Code], [Raw Contract Data].[NAICS Description]

PIVOT format([Raw Contract Data].[Date Signed], "yyyy-mm");

问题是查询将运行但弹出一个框，询问文本27的值 . 它不会从表单上的值中识别它 . 当我手动输入日期（没有前面的PARAMETERS声明）时，WHERE子句将起作用，但当我希望它引用文本字段的值时，我什么也得不到 .

sql ms-access-2010 pivot crosstab

2 回答

  • 1

    试试这个方法，让我知道这是否对你有所帮助

    PARAMETERS EnterDate DateTime;
TRANSFORM nz(Sum([Raw Contract Data].[Action Obligation]), 0) AS [SumOfAction Obligation]
SELECT [Raw Contract Data].[NAICS Code], [Raw Contract Data].[NAICS Description],
Sum([RawContract Data].[Action Obligation]) AS [Total Of Action Obligation],
NZ(round(avg([SumOfAction Obligation]),0),) AS [Average Obligation],
nz(round(stdev([SumOfAction Obligation]),0),0) AS [StDev of CTRs],
nz(round(var([SumOfAction Obligation]),0),0) AS [Variance of Obligations]
FROM [Raw Contract Data]
WHERE [Raw Contract Data].[Date Signed]  <= EnterDate
GROUP BY [Raw Contract Data].[NAICS Code], [Raw Contract Data].[NAICS Description]
PIVOT format([Raw Contract Data].[Date Signed], "yyyy-mm");

    保存此查询，名称为 Query1

    在Filter操作上说 on clicking filter button 定义以下代码

    Dim qdf As DAO.QueryDef
Dim rst as DAO.Recordset
Set qdf = CurrentDb.QueryDefs("Query1")
qdf!EnterDate = Forms!SelectForm!Text27
Set rst = qdf.OpenRecordset
Set rst = Nothing
Set qdf = Nothing
    回复于
  • 0

    请记住，您必须拥有处于打开状态的表单，您可以在其中引用 Text27

    否则，每当执行查询时，查询都不会获取参数值，因为表单未打开，它将询问您 Text27 的值 .

    回复于

相关问题