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
