[AccessD] Figuring the date...opposite request

Thanks Gustav,

 

I hate to make assumptions...but "DatePart("yyyy",[date],1,3)" just doesn't work/ignores the arguments?

 

Thanks,

 

Mark

 


 
> Date: Sat, 7 Mar 2009 20:04:07 +0100
> From: Gustav at cactus.dk
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Figuring the date...opposite request
> 
> Hi Mark
> 
> That depends, but here is a method for the ISO weeknumber standard:
> 
> <code>
> Public Function ISO_WeekYearNumber( _
> ByVal datDate As Date, _
> Optional ByRef intYear As Integer, _
> Optional ByRef bytWeek As Byte) _
> As String
> 
> ' Calculates and returns year and week number for date datDate according to the ISO 8601:1988 standard.
> ' Optionally returns numeric year and week.
> ' 1998-2007, Gustav Brock, Cactus Data ApS, CPH.
> ' May be freely used and distributed.
> 
> Const cbytFirstWeekOfAnyYear As Byte = 1
> Const cbytLastWeekOfLeapYear As Byte = 53
> Const cbytMonthJanuary As Byte = 1
> Const cbytMonthDecember As Byte = 12
> Const cstrSeparatorYearWeek As String = "W"
> 
> Dim bytMonth As Byte
> Dim bytISOThursday As Byte
> Dim datLastDayOfYear As Date
> 
> intYear = Year(datDate)
> bytMonth = Month(datDate)
> bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)
> 
> If bytWeek = cbytLastWeekOfLeapYear Then
> bytISOThursday = WeekDay(vbThursday, vbMonday)
> datLastDayOfYear = DateSerial(intYear, cbytMonthDecember, 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
> 
> ' Adjust year where week number belongs to next or previous year.
> If bytMonth = cbytMonthJanuary Then
> If bytWeek >= cbytLastWeekOfLeapYear - 1 Then
> ' This is an early date of January belonging to the last week of the previous year.
> intYear = intYear - 1
> End If
> ElseIf bytMonth = cbytMonthDecember Then
> If bytWeek = cbytFirstWeekOfAnyYear Then
> ' This is a late date of December belonging to the first week of the next year.
> intYear = intYear + 1
> End If
> End If
> 
> ISO_WeekYearNumber = CStr(intYear) & cstrSeparatorYearWeek & Format(bytWeek, "00")
> 
> End Function
> </code>
> 
> /gustav
> 
> 
> >>> markamatte at hotmail.com 07-03-2009 19:49 >>>
> 
> 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
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Windows Live™ Groups: Create an online spot for your favorite groups to meet.
http://windowslive.com/online/groups?ocid=TXT_TAGLM_WL_groups_032009