本周返回有日期的记录

Question

我有一个返回以下信息的查询；

NAME    | ORDER_NUMBER | DESPATCH_DATE | STOCK_CODE | DESCRIPTION | QTY_ORDER | DUE_DATE
--------+--------------+---------------+------------+--------------+-----------+----------
DigiSoft|     310      |  14/08/2013   |   5112155  | Shampoo      |   1560    |
Test    |      234     |  11/07/2014   |   5113242  | Shampoo      |   213     |
Cast    |      334     |  09/07/2014   |5435421     | Shampoo      |   432     |

SELECT SALES_ORDER.NAME, 
SALES_ORDER.ORDER_NUMBER, 
SALES_ORDER.DESPATCH_DATE, 
SOP_ITEM.STOCK_CODE, 
SOP_ITEM.DESCRIPTION, 
SOP_ITEM.QTY_ORDER, 
SOP_ITEM.QTY_DESPATCH, 
SOP_ITEM.QTY_DELIVERED, 
([QTY_ORDER]-[QTY_DELIVERED]) AS [Quantity left], 
SOP_ITEM.UNIT_PRICE, 
CCur([Quantity left]*[SALES_PRICE]) AS [Value], 
SOP_ITEM.DUE_DATE, SALES_ORDER.DESPATCH_STATUS
FROM STOCK 
INNER JOIN (
SALES_ORDER INNER JOIN SOP_ITEM 
ON SALES_ORDER.ORDER_NUMBER = SOP_ITEM.ORDER_NUMBER) 
ON STOCK.STOCK_CODE = SOP_ITEM.STOCK_CODE
WHERE (((([QTY_ORDER]-[QTY_DELIVERED]))>0) 
AND ((SALES_ORDER.DESPATCH_STATUS)="Part")) 
OR (((SALES_ORDER.DESPATCH_STATUS) Is Null));

我想要能够选择记录的DESPATCH_DATE在这周。

Answer 1

如果您想要日期范围而不是星期号。所以，就像：

WHERE DESPATCH_DATE Between (Date() - WeekDay(Date()) + 1) and (Date() - WeekDay(Date()) + 7) 

Answer 2

您可以使用格式或DatePart：

SELECT Stock.NAME, Stock.ORDER_NUMBER, Stock.DESPATCH_DATE, 
Stock.STOCK_CODE, Stock.DESCRIPTION, Stock.QTY_ORDER, Stock.DUE_DATE
FROM Stock
WHERE Format([despatch_date],"ww") =Format(Date(),"ww") 

小心FirstDayOfWeek：

http://office.microsoft.com/en-ie/access-help/format-function-HA001228839.aspx

http://office.microsoft.com/en-ie/access-help/format-function-HA001228839.aspx