我在MS Access中有一个SQL查询,它生成条形图的数据。此数据包含"01 Jan“格式的数字和月份名称。创建SQL是为了获取过去5个月的数据,包括当前月份的数据。代码从新年开始就不起作用了,如果我把5个月的时间改为3个月,它就可以工作了。我相信这是一个日期格式化的问题,但我似乎无法弄清楚。我对SQL很陌生。有人能帮帮我吗。守则是:
SELECT (Format([INITIATION DATE],"mm")) & " " & (Format([INITIATION DATE],"mmm")) AS Expr1, Sum([Data].[Count Area 1]) AS SumOfCount
FROM [Data]
GROUP BY (Format([INITIATION DATE], "mm")) & " " & (Format([INITIATION DATE], "mmm")), (Format([INITIATION DATE], "yyyy")) & (Format([INITIATION DATE], "mm"))
HAVING ((((Format([INITIATION DATE],"yyyy")) & (Format([INITIATION DATE],"mm")))>=Format(DateAdd("yyyy",0,Date()),"yyyy") & Format(DateAdd("m",-5,Date()),"mm")));我想要的最终结果是

发布于 2020-04-28 11:21:28
我认为您需要一个WHERE子句,而不是HAVING子句。你要的是日期而不是月份的逻辑:
SELECT (Format([INITIATION DATE],"mm")) & " " & (Format([INITIATION DATE],"mmm")) AS Expr1,
Sum([Data].[Count Area 1]) AS SumOfCount
FROM [Data]
WHERE [INITIATION DATE] >= DATEADD("mm", -5, DATE())
GROUP BY (Format([INITIATION DATE], "mm")) & " " & (Format([INITIATION DATE], "mmm"))发布于 2020-04-28 11:36:26
下面的表达式从一个月算起5个月,但不影响年份
Format(DateAdd("yyyy",0,Date()),"yyyy") & Format(DateAdd("m",-5,Date()),"mm")))因此,你要求所有的约会超过'202011‘
我没有MSAccess,但似乎可以将此作为最后一行
HAVING ((((Format([INITIATION DATE],"yyyymm"))>=Format(DateAdd("m",-5,Date()),"yyyymm")));https://stackoverflow.com/questions/61478933
复制相似问题