Mark A Matte
markamatte at hotmail.com
Sat Mar 7 12:49:11 CST 2009
Someone has asked me to do the opposite of what Jennifer is doing. I know I can create a date table...but other than that...
They need to report weekly numbers...and one data point can change periodically...and is not stored historically. I was asked to store this value based on Year and Week. I cannot get the last/first week of 08/09 to work.
I'm using Year: DatePart("yyyy",[date],1,3) and Week: DatePart("ww",[date],1,3) to get the values.
The results below are confusing...how do I get all of the dates to show the same week/year for the last /first week. They don't even care which way it goes...just so they can keep the weeks together.
date year week
12/31/08 2008 52
1/2/09 2009 52
Thanks,
Mark A. Matte
> From: stuart at lexacorp.com.pg
> To: accessd at databaseadvisors.com
> Date: Sun, 8 Mar 2009 00:49:11 +1000
> Subject: Re: [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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
_________________________________________________________________
Express your personality in color! Preview and select themes for Hotmail®.
http://www.windowslive-hotmail.com/LearnMore/personalize.aspx?ocid=TXT_MSGTX_WL_HM_express_032009#colortheme