Dynamically look at a week

[mlocurci]

How can i write a query that looks at a "week" For instance I want to see all request that have appox 10 date fields that fall into next week.

 

[PoopCheese]

Try something like this. StartDate and EndDate are whatever date you want to choose...

SELECT * FROM MyTable WHERE DateField >= #StartDate# AND DateField <= #EndDate#

Good Luck,
Poop

 

[raskew]

If you want to see all records where any of several date fields fall
within a specific week, you could try an adaptation of the following.

I'm using Northwind's Orders table as the example since it has
OrderDate, RequiredDate and ShippedDate as fields.

1. Copy/paste the two functions shown below to a new module in
Northwind.
2. Then paste the query-sql to a new query in Northwind.
3. Run the query and, when prompted, enter a date between
#8/4/94# and #6/1/96#.

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

The query:

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