[AccessD] Need to derive the "Start" and "End" dates from a "DatePart Week Number"

As an alternative, I sometme do this sort of thing;

SELECT DatePart("ww",[SessionDate]) AS WkNum, 
Min(tblSessions.sessiondate) AS StartDate, 
Max(tblSessions.sessiondate) AS enddate, 
Count(tblSessions.SessionPK) AS Sessions
FROM tblSessions
GROUP BY DatePart("ww",[SessionDate]);
 
This gives you the actual first and last order date in each period, so if there are no orders on 
Sat/Sun, the dates given for that week will be Mon-Fri

-- 
Stuart

On 9 Aug 2012 at 14:16, David McAfee wrote:

> Sorry, 2012 was hard coded while testing.
> 
> It should be:
> ?(CDate("01/01/" & YEAR(Date())) - WeekDay("01/01/" & YEAR(Date()),
> vbMonday) )+7 * (DatePart("ww",Date()))
> 
> Now it work Next year too :P
> 
> 
> On Thu, Aug 9, 2012 at 2:04 PM, David McAfee <davidmcafee at gmail.com> wrote:
> 
> > If Weekday starts on Sunday (which I assume it does as per your datepart
> > example below)
> >
> > Week Starting: (CDate("01/01/" & 2012) - WeekDay("01/01/" & 2012,
> > vbMonday))+7 * (DatePart("ww",WeekNumber))
> >
> >  Can test in debug window:
> > ?(CDate("01/01/" & 2012) - WeekDay("01/01/" & 2012, vbMonday))+7 *
> > (DatePart("ww",Date()))
> >
> >
> > HTH
> > David McAfee
> >
> >
> >
> >
> > On Thu, Aug 9, 2012 at 1:43 PM, Brad Marks <BradM at blackforestltd.com>wrote:
> >
> >> All,
> >>
> >> I have a small report that summarizes the number of new orders by week.
> >> The report's underlying query uses "DatePart" like this.
> >>
> >>
> >> OrderWeek: DatePart("ww",[OrderDate])
> >>
> >>
> >>
> >> Here is a small sample of what the report looks like.
> >>
> >> ~~~~~~~~~~~~
> >> Week  Nbr-of-Orders
> >> 16         333
> >> 17         355
> >> ~~~~~~~~~~~~~
> >>
> >> I have been asked to spell out what the start and end dates are for each
> >> week number on the report.
> >>
> >> Is it possible to derive the "Start" and "End" dates from a week number?
> >>
> >> Thanks,
> >> Brad
> >>
> >> --
> >> AccessD mailing list
> >> AccessD at databaseadvisors.com
> >> http://databaseadvisors.com/mailman/listinfo/accessd
> >> Website: http://www.databaseadvisors.com
> >>
> >
> >
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>