Thanks for reviewing it Terry,
That works too (even in Canada!)..
Cheers.
Terry H.
On Thu, 30 Oct 2003 16:45:02 -0000, "Terry Kreft"
<terry.kreft@mps.co.uk> wrote:
[color=blue]
>The code doesn't blindly add 5 what it's doing is using the week offset
>(which happens to be 5 in this case for the UK at least) to calculate when
>the last day is.
>
>Looking at the documentation again, if a value of 5 is returned from the API
>call this actually means the last week of the month rather than an offset
>from the start of the month.
>
>So you could do something like
>
>Private Function GetSundate(intMonth As Integer, _
> intSun As Integer, _
> Optional intYear As Long = -1) _
> As Date
>' Originally submitted by Terry Kreft
>' Modified to set any Year
>
> If intYear = -1 Then intYear = Year(Date)
> ' if not supplied, get this Year
>
> Dim varRet As Variant
> Dim intDayOfWeek As Integer
> Select Case intSun
> Case 5 ' last week of month
> varRet = DateSerial(intYear, intMonth + 1, 1)
> ' avoid regional setting problem
>
> intDayOfWeek = WeekDay(varRet)
> If intDayOfWeek <> 1 Then
> varRet = DateAdd("d", 8 - intDayOfWeek, varRet)
> End If
> varRet = DateAdd("ww", -1, varRet)
>
> Case Else
> varRet = DateSerial(intYear, intMonth, 1)
> ' avoid regional setting problem
>
> intDayOfWeek = WeekDay(varRet)
> If intDayOfWeek <> 1 Then
> varRet = DateAdd("d", 8 - intDayOfWeek, varRet)
> End If
> varRet = DateAdd("ww", intSun - 1, varRet)
> End Select
> GetSundate = varRet
>End Function
>
>Terry Kreft
>
>
>"Terry" <saintkilda@shaw.ca> wrote in message
>news:o360qvojap35kogt23vrviscm0dgqt41df@4ax.com.. .[color=green]
>> I have used the code provided in the PreciseDateDiff function at the
>> following Access Web link to calculate the time change date (from
>> Daylight to Standard time and vice versa):
>>
>>
http://www.mvps.org/access/api/api0024.htm
>>
>> But looks like for this week only, there is a small error in the
>> calculation in the 'GetSundate' function included in the code. You
>> can see what I mean by adding the code below (copied directly from the
>> above link) to an Access module. Then in the debugger, enter:
>>
>> ?StandardTime(2003)
>>
>> The supposed StandardTime change date will display as"
>> 11/2/2003 2:00:00 AM
>>
>> which is obviously not correct. It works correctly for the year 2000
>> and 2004, but the years between are wrong.
>>
>> The problem stems from the following line:
>>
>> varRet = DateAdd("ww", intSun - 1, varRet)
>>
>> I believe the 'GetTimeZoneInformation' call to the operating system
>> returns a value of 5 for intSun in October, and the above blindly adds
>> 4 weeks regardless of the date. Consequently, the date rolls into
>> November sometimes. I have added the following 'if' statement to
>> change the value to a 4 if this happens:
>>
>> If intSun = 5 And Month(DateAdd("ww", intSun - 1, varRet)) = 11 Then
>> intSun = 4
>> End If
>> varRet = DateAdd("ww", intSun - 1, varRet)
>>
>>
>> Just thought others may be interested. I don't know if there is a
>> more elegant solution.
>>
>> NOTE: For users who just use the PreciseDateDiff function as is (to
>> calculate date differences), it may not make a difference, since the 2
>> calulations will cancel each other, but I needed to have the date of
>> the change.
>>
>> Hope this might help anyone else that is using it.
>>
>> Terry.
>>
>>
>> '************************** Code Start ***********************
>> 'This code was originally written by Terry Kreft & Michel Walsh
>> 'It is not to be altered or distributed,
>> 'except as part of an application.
>> 'You are free to use it in any application,
>> 'provided the copyright notice is left unchanged.
>> '
>> 'Code courtesy of
>> 'Terry Kreft & Michel Walsh
>> '
>> Type SYSTEMTIME
>> wYear As Integer
>> wMonth As Integer
>> wDayOfWeek As Integer
>> wDay As Integer
>> wHour As Integer
>> wMinute As Integer
>> wSecond As Integer
>> wMilliseconds As Integer
>> End Type
>>
>> Type TIME_ZONE_INFORMATION
>> Bias As Long
>> StandardName(31) As Integer
>> StandardDate As SYSTEMTIME
>> StandardBias As Long
>> DaylightName(31) As Integer
>> DaylightDate As SYSTEMTIME
>> DaylightBias As Long
>> End Type
>>
>> Declare Function GetTimeZoneInformation Lib "kernel32" _
>> (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
>>
>> Public Function StandardTime(Optional intYear As Long = -1) As Date
>> ' Originally submitted by Terry Kreft
>> ' modified to accept an optinal year
>>
>> If -1 = intYear Then intYear = Year(Date)
>> ' Get this year, by defaut, not -1
>>
>> Dim lngRet As Long
>> Dim TZI As TIME_ZONE_INFORMATION
>> lngRet = GetTimeZoneInformation(TZI)
>> With TZI.StandardDate
>> StandardTime = CVDate(GetSundate(.wMonth, .wDay, _
>> intYear) + (.wHour / 24))
>> End With
>> End Function
>>
>> Private Function GetSundate(intMonth As Integer, _
>> intSun As Integer, _
>> Optional intYear As Long = -1) _
>> As Date
>> ' Originally submitted by Terry Kreft
>> ' Modified to set any Year
>>
>> If intYear = -1 Then intYear = Year(Date)
>> ' if not supplied, get this Year
>>
>> Dim varRet As Variant
>> Dim intDayOfWeek As Integer
>>
>> varRet = DateSerial(intYear, intMonth, 1)
>> ' avoid regional setting problem
>>
>> intDayOfWeek = Weekday(varRet)
>> If intDayOfWeek <> 1 Then
>> varRet = DateAdd("d", 8 - intDayOfWeek, varRet)
>> End If
>> varRet = DateAdd("ww", intSun - 1, varRet)
>> GetSundate = varRet
>> End Function
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>[/color]
>[/color]
