week dates

[kingsgambit]

I have a form with a calendar on, if the user selects a date, a list box with a query attach will show all personnal that have booked holidays from that date plus two weeks.
Is there a function that will calculate the week instead.
i.e if someone asks for the 23/10/02 off then it would show me the records from the begining of the week 20/10/02 to 26/10/02.

 

[Rich]

DatePart("ww",[MyDate]) but then it depends on how you define your weeks

 

[kingsgambit]

I would like to define a week from Monday to Friday.
or would it be easier to have then week starting Sunday ending Saturday
How would I does this

 

[raskew]

That's easily doable. Since Western calendars run
Sunday through Saturday, it would seem logical to
keep with that scheme.

This example applies to the Northwind database, but
should be easily adaptable to any application.

(1) Start by copying / pasting these functions into a new
module in Northwind.

Function NextNDay(ByVal pDay As Date, wday As Integer)
'*****************************************************
'Purpose:   Returns the next n day
'                 (Sunday: 1 through Saturday: 7)
'Author:    raskew
'Inputs:    ? nextNDay(#10/22/02#, 6)
'Output:    10/25/02 -  The next Friday(6)
'           	    following the inputted (pDay) date.
'*****************************************************
NextNDay = [pDay] - WeekDay([pDay]) + wday + IIf(WeekDay([pDay]) >= wday, 7, 0)
End Function
'
'
Function LastNDay(ByVal pDay As Date, wday As Integer)
'*****************************************************
'Purpose:   Returns the previous n day
'                 (Sunday: 1 through Saturday: 7)
'Author:    raskew
'Inputs:    ? lastNDay(#10/22/02#, 1)
'Output:    10/20/02 The previous Sunday(1)
'           	    to the inputted (pDay) date.
'*****************************************************
LastNDay = [pDay] - (WeekDay([pDay]) + IIf(WeekDay([pDay]) <= wday, 7, 0) - wday)
End Function

(2) Next, copy this query-SQL to a new query in Northwind:

PARAMETERS [enter mm/dd/yy] DateTime;
SELECT Orders.OrderID, Orders.CustomerID, 
Orders.OrderDate, 
Format(lastnday([enter mm/dd/yy],1),"ddd") AS startdte, 
Format(nextnday([enter mm/dd/yy],7),"ddd") AS enddte
FROM Orders
WHERE (((Orders.OrderDate) Between 
lastnday([enter mm/dd/yy],1) And 
nextnday([enter mm/dd/yy],7)));

(3) Run the query. When prompted, enter 12/28/95 (sorry, had to use
US short date format-will leave it to you to sort that out). What should
return is all orders between Sunday-Dec 24th and Saturday-Dec 30th. The
last two fields in the query are just to illustrate the range of dates.

(4) The only problem with this is if the user enters 12/24/95 (Sunday). The
response will then be for the date range 12/17/95 through 12/30/95.
Likewise, an entry of 12/30/95 would return records between 12/24/95 and 1/6/96.

(5) I suspect that after studying how this works you'll be able to sort out that minor
glitch.