Hi. I have a link table with a field in GMT time. I want to convert the times to local time in a query. I know that I should use DateAdd, but I am just wondering how to know summer and winter time. On the summer our local time is +2 GMT and on winter +1.
I presume you are talking about the equivlent of USA Daylight Savings Time?
Usually these kick in or out on a specific day of the week, like the first Sunday of April and the last Sunday of October, or some such silly rules. Let's take those two as a possibility. You might need to write a function to do this. If so, make it a PUBLIC function in a general module. Then you could use it in queries.
The first Sunday in April for any year is found by starting with April 1 and stepping through April 7 to see which one is Sunday.
The last Sunday in October for any year is found by starting with October 31 and stepping backwards through October 25 to see which one is Sunday.
So for these two functions, you could write a little loop that takes the year as input and returns either the first Sunday in April or the last Sunday in October for that year, depending on an input parameter of the year number (as a four-digit quantity).
Then you could use the resulting dates in queries and such.
Sorry. I am not talking about the equivlent of USA Daylight Savings Time. In Norway we adjust the clock +1/-1 on summer and winter. On Winter we are +1 GMT and on Summer +2 GMT. The time we adjust the clock is not equal every year. So it is not easy to code the changes.
What I am looking for is to find out the UTC Time in a query. If I can get the UTC time, then I can use DateDiff between UTC time and System time (Local time) and then DateAdd the diff to the field with UTC time.
UHM ??? If you send me the database I will do it for ya...
Come on...
OK...
First copy the full code (excluding maybe the form_load sub)
udtTZI.lngBias will return the number of minutes windows says you are offset vs GMT.
So make a function:
Code:
Function GiveUTC(LocalTime as Date) as date
Dim lngRet As Long
Dim udtTZI As TimeZoneInfo
lngRet = GetTimeZoneInformation(udtTZI)
GiveUTC = DateAdd("N", LocalTime, udtTZI.lngBias)
end function
Offcourse you copy this function on the part where the form_load sub is.
Then in your query:
UTC: GiveUTC([Yourlocaltimefield])
First copy the full code (excluding maybe the form_load sub)
udtTZI.lngBias will return the number of minutes windows says you are offset vs GMT.
So make a function:
Code:
Function GiveUTC(LocalTime as Date) as date
Dim lngRet As Long
Dim udtTZI As TimeZoneInfo
lngRet = GetTimeZoneInformation(udtTZI)
GiveUTC = DateAdd("N", LocalTime, udtTZI.lngBias)
end function
Offcourse you copy this function on the part where the form_load sub is.
Then in your query:
UTC: GiveUTC([Yourlocaltimefield])
Please re-read the post, and not the bold parts... WHO is talking about forms? Yes the referenced post is using a form for making the msgbox, here I have handed you your solution for the query.
Sorry for my limited knowledge, but I do not understand where to put the code when I don't use any form.
I have made a module with the code (Without Sub_Form_Load) and named it GiveUTC. I have also pasted the function in the same module. Than I have made a field in the query "UTC: GiveUTC([TimeInUTC])" (TimeInUTC is the field with the time information which is in UTC Zone). I get "Undefined function \GiveUTC\ in expression." So I am confused!
Now, I have made a module with the code and the function. When I test it in a form, I get -60 as "Adjust your time value", but We are in the timezone +1 GMT. Tha fact is that now is +2 hours GMT. In the function I have defined LocalTime as Time(). Is that right?
Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Private Type TIME_ZONE_INFORMATION
Bias As Long
StandardName As String * 32
StandardDate As Long
StandardBias As Long
DaylightName As String * 32
DaylightDate As Long
DaylightBias As Long
End Type
Function NowPlusTZBias() As Date
Dim usrTZI As TIME_ZONE_INFORMATION
Dim lngRetVal As Long
lngRetVal = GetTimeZoneInformation(usrTZI) ' hours +/-
NowPlusTZBias = Now + (usrTZI.Bias / 1440) ' minutes in +/-
End Function
Now, I have made a module with the code and the function. When I test it in a form, I get -60 as "Adjust your time value", but We are in the timezone +1 GMT. Tha fact is that now is +2 hours GMT. In the function I have defined LocalTime as Time(). Is that right?
Well the code provided dit state GetUTC which obviously says go from local time to UTC, not vice versa.... There you offcourse need to reverse the + and the - signs...
Other than that your "new" code is 100% the same as the code in the linked post and 'my' function combined. So you did get it to work... good for you... Who did you get it from tho? Allways nice to list the author...
If you send me the database I will do it for ya... 
