MS Access 查询问题？

Question

我正在使用此查询：

SELECT D.Generic, D.Ww, D.Dd, D.Plan, c.TotalScan, D.Plan - c.TotalScan AS Balance
  FROM TableA D
       LEFT JOIN (
                  SELECT COUNT(a.Specific) AS TotalScan,
                         b.Generic, a.Dd,a.Ww  
                    FROM TableB a 
                         INNER JOIN TableC b 
                            ON a.Specific = b.Specific
                   GROUP 
                      BY b.Generic,a.Dd,a.Ww 
                   WHERE DATEDIFF(DAY, a.TransactionDate, GETDATE()) = 0
                 ) c
          ON c.Generic = D.Generic 
             AND D.Ww = c.Ww 
             AND c.Dd = D.Dd
 WHERE DATEDIFF(DAY, c.TransactionDate, GETDATE()) = 0;

过滤在我的 sqlserver 数据库中插入的所有记录。

现在我很难做到MS访问。

1. DATEDIFF(Day, TransactionDate, GetDate()) = 0  -- Not Work on MS Access(Which Filter all Records inserted in current Date) 
2. Cant display TotalScan from subquery

输出日期示例：

TransactionDate
3/21/2011 7:26:24 AM
3/21/2011 7:26:24 AM
3/22/2011 7:26:24 AM --
3/22/2011 7:26:28 AM --
3/22/2011 7:26:30 AM --
3/22/2011 7:26:32 AM --
3/22/2011 7:26:35 AM --

如果我今天的日期是 3/22/2011，将显示 5 条记录。

谢谢问候

Answer 1

GetDate() 是 SQL Server 特定的，Access 有 Now()。

Access 中也存在 DateDiff() 函数，但间隔的参数不同：

DateDiff("d", TransactionDate, Now())

Answer 2

相当于：

DATEDIFF(DAY, c.TransactionDate, GETDATE()) = 0

DATEDIFF("d", c.TransactionDate, Now()) = 0 

问候