SQL 查询 Access - 显示截至 9 月底的记录

Question

我不确定如何显示到 9 月底的记录。

到目前为止，我的代码似乎因错误“条件表达式中的数据类型不匹配”而失败。

SELECT Month(InvoiceDate) AS IncomeMonth, Sum(TotalPrice) AS Income
FROM INVOICE
WHERE InvoiceDate <= '30/9/2018'
GROUP BY Month(InvoiceDate);

示例数据:

InvoiceDate | TotalPrice
  05/11/18  |    $200
  05/04/18  |    $100
  05/05/18  |    $150
  05/03/18  |    $120
  05/07/18  |    $100

预期输出查询结果:

IncomeMonth | Income
         4  |    $100 
         6  |    $150
         3  |    $120
         7  |    $100
         3  |    $120
         7  |    $100

Answer 1

因为您可能希望在下个月运行类似的查询，所以将其设为动态:

SELECT 
    Month(InvoiceDate) AS IncomeMonth, Sum(TotalPrice) AS Income
FROM 
    INVOICE
WHERE 
    InvoiceDate < DateSerial(Year(Date()), Month(Date()), 1)
GROUP BY 
    Month(InvoiceDate);