[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
>