我需要计算一年中处理了多少订单,但是我有一个名为 datePrepped 的字段,其中我有日期,以便我使用函数 Month(( 来获取我需要在一年中的所有月份拥有这个的月份
我不想分组我需要将输出存储在变量中,这就是我使用 AS 1 月 AS 2 月等的原因,但是我收到此错误 [Microsoft][ODBC Microsoft访问驱动程序] 参数太少。预期 1.
sqlString = "SELECT " & _
"SUM(IIf(Month(r.datePrpped)='1', 1, 0)) AS Jan," & _
"SUM(IIf(Month(r.datePrpped)='2', 0)) AS Feb," & _
"SUM(IIf(Month(r.datePrpped)='3', 0)) AS Mar," & _
"SUM(IIf(Month(r.datePrpped)='4', 0)) AS Apr," & _
"SUM(IIf(Month(r.datePrpped)='5', 0)) AS May " & _
"FROM OrderControl AS r;"
所需输出示例
<%=MyRecordset("Jan")%> HTML output 500
<%=MyRecordset("Feb")%> HTML output 800
您在大多数iif()
表达式中都缺少'1'
:
SELECT SUM(IIf(Month(r.datePrpped) = 1, 1, 0)) AS Jan,
SUM(IIf(Month(r.datePrpped) = 2, 1, 0)) AS Feb,
SUM(IIf(Month(r.datePrpped) = 3, 1, 0)) AS Mar,
SUM(IIf(Month(r.datePrpped) = 4, 1, 0)) AS Apr,
SUM(IIf(Month(r.datePrpped) = 5, 1, 0)) AS May
FROM OrderControl AS r;
MONTH()
函数返回一个数字,因此比较值适合于数字,而不是字符串。