[AccessD] Figuring the date

I agree

You are replacing a Date + Long operation with a Long + Long operation which has to be 
more efficient

-- 
Stuart


On 7 Mar 2009 at 14:55, Gustav Brock wrote:

> Hi Stuart
> 
> Of course, how could I miss that - but I think I still would prefer to pass the day count to DateSerial:
> 
>   DateSerial(intYear, 1, 1 - Weekday(DateSerial(intYear, 1, 1), vbTuesday) + 7 * intWeek)
> 
> /gustav
> 
> 
> >>> stuart at lexacorp.com.pg 07-03-2009 13:53 >>>
> A further small optimization:
> 
> Replace ... 1+7 ..............+7*(intWeek - 1)
> with       .....1+................. +7*(intWeek)
> 
> i.e.
> DateSerial(intYear, 1, 1 - Weekday(DateSerial(intYear, 1, 1), vbTuesday)) + 7 * (intWeek)
> 
> 
> -- 
> Stuart
> 
> On 7 Mar 2009 at 11:11, Gustav Brock wrote:
> 
> > Hi Drew, the DateSerial geek
> > 
> > Adding the count of days to the day parameter of DateSerial and not to the resulting date, it can be reduced to:
> > 
> > Function MondayDate(intYear As Long, intWeek As Long) As Date
> >   MondayDate = DateSerial(intYear, 1, 1 + 7 - Weekday(DateSerial(intYear, 1, 1), vbTuesday) + 7 * (intWeek - 1))
> > End Function
> > 
> > For everyone else: Note Drew's clever use of vbTuesday to turn Weekday into a kind of modulus function. If you would prefer vbMonday as the parameter in Weekday, you would have to use modulus or you would an additional week when Jan. 1 is the first day of the year:
> > 
> >   MondayDate = DateSerial(intYear, 1, (8 - Weekday(DateSerial(intYear, 1, 1), vbMonday)) Mod 7 + 1 + 7 * (intWeek - 1))
> > 
> > /gustav
> > 
> > >>> DWUTKA at marlow.com 06-03-2009 23:46 >>>
> > Function MondayDate(intYear As Long, intWeek As Long)
> > MondayDate = DateSerial(intYear, 1, 1 + (7 - Weekday(DateSerial(intYear,
> > 1, 1), vbTuesday))) + (7 * (intWeek - 1))
> > End Function
> > 
> > Watch for word wrap there, the code inside that function should be all
> > one line.
> > 
> > It's the date of January 1st of whatever year, offset by when the first
> > Monday is, the 7-Weekday bit, plus 7*one less then the number of weeks.
> > 
> > So if you put MondayDate(2009,1), you'll get 1-5-2009, the first Monday
> > of the year.  MondayDate(2009,52) and you'll get 12-28-2009, the last
> > Monday of the year.
> > 
> > Drew
> > 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com 
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer
> > Gross
> > Sent: Friday, March 06, 2009 2:38 PM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Figuring the date
> > 
> > Okay, I have answers to my questions - they define Week 1 as the first
> > full week of the year and the week is defined as Monday to Sunday.  Any
> > thoughts on how to get that Monday date when I know the year and the
> > week #?
> > 
> > Jennifer
> > 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com 
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer
> > Gross
> > Sent: Friday, March 06, 2009 12:34 PM
> > To: AccessD List
> > Subject: [AccessD] Figuring the date
> > 
> > 
> > Hey Everyone,
> > 
> > I have two pieces of information - the year and the week # within the
> > year, for instance 2008 13 is the 13th week in 2008.  I have questions
> > out to the client about how they define the week (Sun-Sat, Mon-Sun) and
> > how the first week of the year is defined - week that January 1 falls in
> > or first full week of the year.  Anyway - what I need to do is create a
> > function, unless one already exists, when fed these two pieces of
> > information returns the Monday date (short date format) for that week.  
> > 
> > Any help is appreciated.
> > 
> > Jennifer
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com