访问交叉表中的日期范围-Java 学习之路

尝试获取交叉表以在打开查询时显示提示以允许输入日期范围（开始和结束）（dd-mm-yyyy），以便在运行查询时仅返回此数据 .

目前坐在下面的代码;

TRANSFORM Count(AlphaData.[Invoice]) AS CountOfInvoice
SELECT AlphaData.[Reason], Count(AlphaData.[Invoice]) AS [Total Of Invoice]
FROM AlphaData
WHERE ((AlphaData.[DateRaised]) Between AlphaData.[DateRaised] And AlphaData.[DateRaised])
GROUP BY AlphaData.[Reason]
PIVOT Format([DateRaised],"Short Date");

但是不能为我的生活绕过“MS Access数据库引擎无法识别'Alphadata . [DateRaised：]'作为有效的字段名称或表达式”问题 .

查询的“WHERE”部分在其他查询中起作用，但它只是在交叉表中应用时才会显示 .

有什么建议？

2 回答

看起来有点混乱 . 怎么样：

PARAMETERS
    [From Date:] DateTime,
    [To Date:] DateTime;
TRANSFORM 
    Count(*) AS CountOfInvoice
SELECT 
    AlphaData.[Reason], 
    Sum(AlphaData.[Invoice]) AS [Total Of Invoice]
FROM 
    AlphaData
WHERE 
    AlphaData.[DateRaised] Between [From Date:] And [To Date:]
GROUP BY 
    AlphaData.[Reason]
PIVOT 
    Format([DateRaised],"Short Date");

回复于 2024-04-29T07:56:20+08:00

您需要将参数添加到查询中：

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Count(AlphaData.Invoice) AS CountOfInvoice
SELECT AlphaData.Reason, Count(AlphaData.Invoice) AS [Total Of Invoice]
FROM AlphaData
WHERE (((AlphaData.DateRaised) Between [Start Date] And [End Date]))
GROUP BY AlphaData.Reason
PIVOT Format([DateRaised],"Short Date");

（ note: 参数作为第一行添加，然后在 Between 语句中使用） .

如果您正在使用图形界面，则需要查找“参数”选项：

enter image description here

并在对话框中输入您的参数：

enter image description here

我没有意识到这会发生在交叉表中，因为您可以只为select查询键入参数：

SELECT Invoice, Reason, DateRaised
FROM AlphaData
WHERE DateRaised=[Start Date]

回复于 2024-04-29T07:56:20+08:00

访问交叉表中的日期范围

2 回答

相关问题