[AccessD] Figuring the date

Jennifer Gross jengross at gte.net
Fri Mar 6 15:24:10 CST 2009


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





More information about the AccessD mailing list