CODEWITHSUNDEEP
ms-accessvba
Chrislaar123
Chrislaar123

Reputation: 321

Returning records with dates this week

I have a query that returns the following information;

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));

I want to be able to select records where the DESPATCH_DATE is in this week.

Upvotes: 0

Views: 58

Answers (2)

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

If you want the date range and not the week number. So something like:

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

Upvotes: 2

Fionnuala
Fionnuala

Reputation: 91316

You can use Format or 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")

Be careful about 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

Upvotes: 0

Related Questions