[AccessD] Week of the Month - SOLVED

Hi Gustav,

Hmmm, nice one liner...I wasn't aware of the DatePart() function. Very
cool...

Thanks
Vlad

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, 20 June 2003 8:19 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Week of the Month - SOLVED


Hi Vlad

That's a long way to go as the built in function DatePart() returns this
for any date of any year (within the date range of datatype DateTime, of
course) in one line of code:

  intWeekNumber = DatePart("ww", datDate, vbMonday, vbFirstFourDays)

It is a little buggy for leap years where it can mix up week 53 and week
1. That may be critical; for those cases we use this function:

<code>

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,
Denmark. ' May be freely used and distributed.

  Dim bytWeek As Integer
  Dim intYear As Integer
  Dim bytISO_Thursday As Byte

  bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)
  If bytWeek = 53 Then
    bytISO_Thursday = WeekDay(vbThursday, vbMonday)
    intYear = Year(datDate)
    If WeekDay(DateSerial(intYear, 12, 31), vbMonday) >= bytISO_Thursday
Then
      ' OK, week count of 53 is caused by leap year.
    Else
      ' Correct for Access97/2000 bug.
      bytWeek = 1
    End If
  End If
  
  ISO_WeekNumber = bytWeek

End Function

</code>

I wonder if this bug i still present in Access XP?

/gustav


> Sorry I should have explained myself better I was assuming that 
> January 1st was a Monday etc etc. Here is a cool solution I found at 
> http://www.cpearson.com/excel/DateTimeVBA.htm

> Does what I need:

> Public Function YearStart(WhichYear As Integer) As Date

> Dim WeekDay As Integer
> Dim NewYear As Date

> NewYear = DateSerial(WhichYear, 1, 1)
> WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0

> If WeekDay < 4 Then
>     YearStart = NewYear - WeekDay
> Else
>     YearStart = NewYear - WeekDay + 7
> End If

> End Function

> Public Function ISOWeekNum(AnyDate As Date, Optional WhichFormat As
> Variant) As Integer
> ' WhichFormat: missing or <> 2 then returns week number,
> '                                = 2 then YYWW
> '
> Dim ThisYear As Integer
> Dim PreviousYearStart As Date
> Dim ThisYearStart As Date
> Dim NextYearStart As Date
> Dim YearNum As Integer

> ThisYear = Year(AnyDate)
> ThisYearStart = YearStart(ThisYear)
> PreviousYearStart = YearStart(ThisYear - 1)
> NextYearStart = YearStart(ThisYear + 1)
> Select Case AnyDate
>     Case Is >= NextYearStart
>         ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
>         YearNum = Year(AnyDate) + 1
>     Case Is < ThisYearStart
>         ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
>         YearNum = Year(AnyDate) - 1
>     Case Else
>         ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
>         YearNum = Year(AnyDate)
> End Select

> If IsMissing(WhichFormat) Then Exit Function
> If WhichFormat = 2 Then
>     ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
>     Format(ISOWeekNum, "00"))
> End If

> End Function

> Thanks for everyones suggestions...

> Regards
> Vlad

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav 
> Brock
> Sent: Friday, 20 June 2003 1:27 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Week of the Month


> Hi Vlad

> Are you sure about this? Normally, an ISO standard, a week runs from 
> Monday to Sunday (except in the US where they start the week in the 
> middle of the weekend - go figure).

> The DatePart() function and Weekday() will give the week and weekdays 
> if that is what you need (so you say). DatePart() is a little buggy 
> around week 52 but that won't probably be of importance to you.

> /gustav


>> Does anyone know of a method to work out what week it is of the 
>> month. For example, you have a range of  dates 1st January to 31st 
>> March. The 1st to the 7th is the 1st week of January and the 8th to 
>> the 14th is the second etc etc...
 
>> I have 2.5 years of data (about 1 Million rows) from an old main 
>> frame system and we are trying to determine the frequency of delivery

>> to each customer on a per monthly basis. So effectively to say 
>> customer Jim Spanner the client delivers every second Thursday for 
>> February. Is there an elegant method of working this out? I have the 
>> date the delivery was carried out for the past 2.5 years, but am 
>> having difficulty working out how I could manufacture a frequency 
>> pattern.

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com