[AccessD] DatePart "WW"

Thanks Gustav,

I will definetly keep this in mind for larger projects...

For this instance...I just needed to group by week for the last 6 
months...and a single day was 'biting' me.  I almost just changed the 
date...lol...but my 'data integrity morals' said that it probably wasn't the 
best thing to do.

Thanks,

Mark A. Matte


>From: "Gustav Brock" <Gustav at cactus.dk>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: <accessd at databaseadvisors.com>
>Subject: Re: [AccessD] DatePart "WW"
>Date: Thu, 24 May 2007 16:44:23 +0200
>
>Hi Mark
>
>If you are looking for ISO week numbers, use these constants:
>
>   bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)
>
>However, DatePart has always - since Access 1.0 - been buggy for week 
>number 53.
>Thus, for serious business use, use a function like this which I have 
>posted many times:
>
>Public Function ISO_WeekNumber( _
>   ByVal datDate As Date) _
>   As Byte
>
>' Calculates and returns week number for date datDate according to the ISO 
>8601:1988 standard.
>' 1998-2000, Gustav Brock, Cactus Data ApS, CPH.
>' May be freely used and distributed.
>
>   Const cbytFirstWeekOfAnyYear  As Byte = 1
>   Const cbytLastWeekOfLeapYear  As Byte = 53
>
>   Dim bytWeek                   As Byte
>   Dim bytISOThursday            As Byte
>   Dim datLastDayOfYear          As Date
>
>   bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)
>
>   If bytWeek = cbytLastWeekOfLeapYear Then
>     bytISOThursday = WeekDay(vbThursday, vbMonday)
>     datLastDayOfYear = DateSerial(Year(datDate), 12, 31)
>     If WeekDay(datLastDayOfYear, vbMonday) >= bytISOThursday Then
>       ' OK, week count of 53 is caused by leap year.
>     Else
>       ' Correct for Access97/2000 bug.
>       bytWeek = cbytFirstWeekOfAnyYear
>     End If
>   End If
>
>   ISO_WeekNumber = bytWeek
>
>End Function
>
>/gustav
>
> >>> markamatte at hotmail.com 24-05-2007 16:14 >>>
>Hello All,
>
>I'm using datepart to get the week.  12/31/2006 I am having an issue...No
>matter what optional constant I use or change...I get Day1 of week 53... 
>and
>1/1/2007 shows as Day2 of week 1.  How do I get Day1 of Week1?
>
>Thanks,
>
>Mark A. Matte
>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
More photos, more messages, more storage—get 2GB with Windows Live Hotmail. 
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_2G_0507