I know that it exists in our system somewhere, but its not in VBA.

Anybody have that code anywhere? or know where i can get it?

thanks

sportsguy

Maybe this link (http://www.mvps.org/access/api/api0024.htm) will help.

Access hack. . . though I can key a table correctly. .

If I can figure out whether a random date is within
daylight savings time or not, then I can take GMT, which is
provided, and back into the correct local time. . .

I just can't figure out how this evaluates daylight saving time
or not. . .

sorry, this is beyond me at the moment. . .

thanks

What I would like is to take Zulu date/time, GMT, UCT date/time,
and convert it to local date/time. then examine local time and adjust for daylight savings time. . .

that way, I can get my fiscal close date and time correct.

unfortunately, I don't know how to interpret the TZI information retrieved through the api

thanks
sportsguy

I added some Debug.Print statements to the code so you can see what is returned in the immediate window. Put this *all* in a separate module and then play with it in the immediate window.
Option Compare Database
Option Explicit

'************************** 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

Function PreciseDateDiff(Interval As String, ByVal Date1, ByVal Date2, _
Optional FirstDayOfWeek As Integer = vbSunday, _
Optional FirstWeekOfYear As Integer = vbFirstJan1) _
As Long
'From an original idea by Michel Walsh
'Get a DateDiff, taking into account the time light saving
'
'Usage Example:
'
' ? PreciseDateDiff("h", #1/1/90#, #5/5/98#)
'
Dim lngRet As Long
Dim x As Integer
Dim TZI As TIME_ZONE_INFORMATION
Dim strEval As String
If Eval("'" & Interval & "' in ('h','n','s')") Then
If FirstDayOfWeek >= 0 And FirstDayOfWeek <= 7 Then
If FirstWeekOfYear >= 0 And FirstWeekOfYear <= 3 Then
lngRet = GetTimeZoneInformation(TZI)
strEval = DateForSQL(Date1) & " between " _
& DateForSQL(SummerTime(Year(Date1))) & " and " _
& DateForSQL(StandardTime(Year(Date1)))
If Eval(strEval) Then
Date1 = DateAdd("n", TZI.DaylightBias, Date1)
End If
strEval = DateForSQL(Date2) & " between " _
& DateForSQL(SummerTime(Year(Date2))) & " and " _
& DateForSQL(StandardTime(Year(Date2)))

'-- Add this code in and play with it in the immediate window.
'--
'-- I just put:
'-- ? PreciseDateDiff("h", #1/1/90#, #5/5/98#)
'-- in the immediate window to see what was going on.
'--
'-- I still haven't figured out everything but thought you could play with it.

Debug.Print "Current Bias " & TZI.Bias & " minutes or " & TZI.Bias / 60 & " hours."
For x = 0 To 30
Debug.Print Chr(TZI.DaylightName(x));
Next
Debug.Print
Debug.Print "DaylightBias " & TZI.DaylightBias & " minutes or "; TZI.DaylightBias / 60 & " hour(s)."
Debug.Print " Daylight Savings starts"
Debug.Print "DaylightDate.wDayOfWeek [" & TZI.DaylightDate.wDayOfWeek & "]"
Debug.Print "DaylightDate.wMonth [" & TZI.DaylightDate.wMonth & "]"
Debug.Print "DaylightDate.wDay [" & TZI.DaylightDate.wDay & "]"
Debug.Print "DaylightDate.wHour [" & TZI.DaylightDate.wHour & "]"
Debug.Print "DaylightDate.wMinute [" & TZI.DaylightDate.wMinute & "]"
Debug.Print "DaylightDate.wSecond [" & TZI.DaylightDate.wSecond & "]"
Debug.Print "DaylightDate.wMilliseconds [" & TZI.DaylightDate.wMilliseconds & "]"

For x = 0 To 30
Debug.Print Chr(TZI.StandardName(x));
Next
Debug.Print
Debug.Print "StandardBias " & TZI.StandardBias & " minutes or "; TZI.StandardBias / 60 & " hour(s)."
Debug.Print " Daylight Savings ends"
Debug.Print "StandardDate.wDayOfWeek [" & TZI.StandardDate.wDayOfWeek & "]"
Debug.Print "StandardDate.wMonth [" & TZI.StandardDate.wMonth & "]"
Debug.Print "StandardDate.wDay [" & TZI.StandardDate.wDay & "]"
Debug.Print "StandardDate.wHour [" & TZI.StandardDate.wHour & "]"
Debug.Print "StandardDate.wMinute [" & TZI.StandardDate.wMinute & "]"
Debug.Print "StandardDate.wSecond [" & TZI.StandardDate.wSecond & "]"
Debug.Print "StandardDate.wMilliseconds [" & TZI.StandardDate.wMilliseconds & "]"

If Eval(strEval) Then
Date2 = DateAdd("n", TZI.DaylightBias, Date2)
End If
lngRet = DateDiff(Interval, Date1, Date2, _
FirstDayOfWeek, FirstWeekOfYear)
PreciseDateDiff = lngRet
End If
End If
Else
PreciseDateDiff = DateDiff(Interval, Date1, Date2, FirstDayOfWeek, FirstWeekOfYear)
End If
End Function

Private Function DateForSQL(dteDate) As String
DateForSQL = Format(dteDate, "\#m/dd/yyyy h:nn:ss AM/PM \#")
End Function


Public Function SummerTime(Optional intYear As Long = -1) As Date
' Originally submitted by Terry Kreft
' modified to accept an optional 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.DaylightDate
SummerTime = CVDate(GetSundate(.wMonth, .wDay, _
intYear) + (.wHour / 24))
End With
End Function

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
'************************** Code End ***********************

thanks

that's exactly what i spent 3 hours last night doing with that code.

turns out that we use a standard offset of EST or -5h
so I just have to use dateadd function, and I am all set.

However, it still would be a cool piece of code to write

thanks

sportsguy

I've been using this code for a database that is currently being used in New York, London and India. The New York and India users are having no problems, but the London users are now reporting that it still thinks they're on DST. It appears the StandardDate() function is returning November 4, 2007, which is the date DST will end in the U.S., but not the U.K. Does anyone know why this would be?

Hi -

See if this is of any help:

http://www.uic.edu/depts/accc/systems/daylight/windows.html

Bob