David McAfee
davidmcafee at gmail.com
Fri Mar 6 15:37:42 CST 2009
ok, I just looked through help and saw the dirst day of week & first week of
year are parameters in the DatePart function), so the function should
actually be:
Public Function GetMonday(intYr As Integer, intWkNo As Integer) As Date
GetMonday = DateAdd("d", 2 - DatePart("w", DateAdd("ww", intWkNo, "1/1/" &
intYr), vbSunday, vbFirstFullWeek), DateAdd("ww", intWkNo, ("1/1/" &
intYr)))
End Function
On Fri, Mar 6, 2009 at 1:24 PM, Jennifer Gross <jengross at gte.net> wrote:
> Hi David,
>
> Yes, it is weird. The data is coming to them from a source they cannot
> control and they need to be able to translate it.
>
> Jennifer
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
> Sent: Friday, March 06, 2009 1:21 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Figuring the date
>
>
> Yeah, it's really weird to have the year and week number, and not have
> the actual date, isnt it?
>
> Public Function GetMonday(intYr As Integer, intWkNo As Integer) As Date
> GetMonday = DateAdd("d", 2 - DatePart("w", DateAdd("ww", intWkNo,
> ("1/1/" & intYr))), DateAdd("ww", intWkNo, ("1/1/" & intYr))) End
> Function
>
>
> a quick test from the immediate window:
>
> ? GetMonday(2009,10)
> 3/9/2009
>
>
> I dont know the Access equivalent to DATEFIRST, so I'm not sure if it
> works of the OS's regional settings or not.
>
> HTH
> David
>
> On Fri, Mar 6, 2009 at 1:12 PM, Charlotte Foust
> <cfoust at infostatsystems.com>wrote:
>
> > Believe me, it's much easier to work with a date table for this kind
> > of thing that any SQL solution you can come up with. You do, in fact
> > figure it on the fly because you query the data based on the related
> > periods in the date table. I think I have some code lying around to
> > create a date table if you want to try it.
> >
> > Charlotte Foust
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com [mailto:
> > accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer Gross
> > Sent: Friday, March 06, 2009 1:05 PM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Figuring the date
> >
> > Thanks Gary. If I can figure a way to do it on the fly that is my
> > preference. Populating that table would be a pain without a way to
> > figure the dates anyway.
> >
> > Jennifer
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos
> > Sent: Friday, March 06, 2009 12:50 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Figuring the date
> >
> >
> > If it's for a finite group of years I would use a date table. It's the
>
> > data warehouse way for things like that and I work in the data
> > warehouse group, hence my leaning towards that. You pre-load the date
> > table and your good. Until the pre-loaded dates pass by of course.
> >
> > GK
> >
> > On Fri, Mar 6, 2009 at 2:38 PM, Jennifer Gross <jengross at gte.net>
> > wrote:
> > > 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 No virus found in this
> > > incoming message. Checked by AVG - www.avg.com
> > > Version: 8.0.237 / Virus Database: 270.11.8/1986 - Release Date:
> > > 03/05/09 19:32:00
> > >
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> >
> > --
> > Gary Kjos
> > garykjos at gmail.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > No virus found in this incoming message.
> > Checked by AVG - www.avg.com
> > Version: 8.0.237 / Virus Database: 270.11.8/1986 - Release Date:
> > 03/05/09 19:32:00
> >
> >
> >
> > --
> > 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.0.237 / Virus Database: 270.11.8/1986 - Release Date:
> 03/05/09 19:32:00
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>